/*!
Copyright (c) 2012
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

    Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
    Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
(function(factory, context, baseLibId){

    //actual dependcies are: re_base, re_sqlparser.
    //but since re_sqlparser loads re_base, just specify re_sqlparser.
    var deps = [
            {id : 're_sqlparser', lib : context[baseLibId] ? context[baseLibId].sqlParser : undefined}
        ]
         ,i, len, dep = '', missing = [];

    for(i = 0, len = deps.length; i  < len; i++){
        dep = deps[i];      
        if( undefined === dep.lib ){
            missing = missing.concat([dep.id]);
        }            
    }
    
    if( missing.length > 0 ){
    
        if( typeof define === 'function' && define.amd ){
            
            define(missing, function(){
                var baseLib = context[baseLibId];
                factory(baseLib, context);
                return baseLib.webSQL;
            });
            
            return;
        }
        
        throw new Error('re_websql requires the following libraries: ' + missing.join(','));
        
    }
    
    factory(context[baseLibId], context);
        
}(function(baseLib, context){

var

    _hasNoNativeWebSQL = ('function' === typeof context.openDatabase2) || (undefined === context.openDatabase)
    
    ,SQLError, SQLResultSetRowList, SQLResultSet, SQLTransaction, SQLDatabase, sqlParser = baseLib.sqlParser
    ,QueryOptimizer, AbstractWebStorage, LocalStorage, IndexedDB
    
    ,_SQLErrorCodes = {
        UNKNOWN_ERR : 0
       ,DATABASE_ERR : 1
       ,VERSION_ERR : 2
       ,TOO_LARGE_ERR : 3
       ,QUOTA_ERR : 4
       ,SYNTAX_ERR : 5
       ,CONSTRAINT_ERR : 6
       ,TIMEOUT_ERR : 7
    }
    
    ,_storageDriverToUse = function(){
    
        return (context.indexedDB || context.mozIndexedDB || context.webkitIndexedDB || context.msIndexedDB) 
            ? "IndexedDB"
            : "LocalStorage";
    };


baseLib.apply(baseLib, {

    webSQL : {
    
        // @method: noNativeWebSQL
        // @returns: boolean
        // Returns TRUE if the current context does not natively support WebSQL; FALSE if otherwise.
        noNativeWebSQL : function(){
        
            return _hasNoNativeWebSQL;
        }
       
       ,storageDriverToUse : _storageDriverToUse        
       
       ,SQLErrorCodes : _SQLErrorCodes
      
       ,INVALID_STATE_ERR :  undefined
       
       ,SECURITY_ERR : undefined
       
        /**
        @method: openDatabase

        @returns: Database
        */    
       ,openDatabase : context.openDatabase || function(shortName, version, displayName, maxSize){
        
            var storageDriver = _storageDriverToUse();
           
            return new SQLDatabase({
                shortName: shortName
               ,dbName: shortName
               ,version: version
               ,displayName: displayName
               ,maxSize: maxSize
               ,storageDriver: storageDriver
            });
            
        }
    
       /**
        @method: openDatabase2

        @returns: Database
        */
       ,openDatabase2 : function(cfg) {
            
            if( _hasNoNativeWebSQL === false ){
                throw new Error("Using openDatabase2 is unecessary since WebSQL is natively supported. Use openDatabase instead.");
            }
            
            return new SQLDatabase(cfg);
       }
   
    }
    
});

//no need to continue if WebSQL is natively supported.
if( false === _hasNoNativeWebSQL ){
    return;
}

var fnNameRemove = (function(fnGivenName){

    var arrPrototype = Array.prototype, cnt = 0, _fnName = fnGivenName;
        
    if( arrPrototype.fnNameRemove !== undefined ){
        return arrPrototype.fnNameRemove;
    }
    
    if( arrPrototype[_fnName] ){
        while( arrPrototype[_fnName] !== undefined ){
            _fnName = fnGivenName + (++cnt);
        }
    }    

    arrPrototype.fnNameRemove = _fnName;
    
    return _fnName;

}('remove'));

/**
Array Remove - By John Resig (MIT Licensed)

Remove the second item from the array
array.remove(1);

Remove the second-to-last item from the array
array.remove(-2);

Remove the second and third items from the array
array.remove(1,2);

Remove the last and second-to-last items from the array
array.remove(-2,-1);

(from: http://stackoverflow.com/a/9815010)
Note: It must be ensured that the name given to Array Remove would not conflict with any existing property in Array's prototype.
A conflict might result to unpredictable behavior.
*/
Array.prototype[fnNameRemove] = function(from, to){
  
  var rest = this.slice((to || from) + 1 || this.length);
  this.length = from < 0 ? this.length + from : from;
  return this.push.apply(this, rest);
  
};

/**
@class: SQLError

See: http://www.w3.org/TR/webdatabase/#sqlerror
*/
SQLError = baseLib.extend(

    baseLib.apply({

        message : ''
   
       ,code : -1
   
       ,init : function(msg, code){
            
            this.message = msg  || '';
            this.code       = code === undefined ? -1 : code;
            
       }
      
      ,toString : function(){
        return "[object SQLError]";
      }
      
    }, _SQLErrorCodes)
    
);
    
/**
@class: SQLResultSetRowList

See: http://www.w3.org/TR/webdatabase/#sqlresultsetrowlist
*/
SQLResultSetRowList = baseLib.extend({

    length : 0

    ,_data : []

    ,init : function(data){
        this._data = data;
        this.length = data.length;
    }

    ,item : function(index){

        return this._data[index] || null;
    }

    ,toString : function(){

        return "[object SQLResultSetRowList]";
    }
  
});
   
/**
@class: SQLResultSet

See: http://www.w3.org/TR/webdatabase/#sqlresultset
*/
SQLResultSet = baseLib.extend({

    insertId : 0
    
   ,rowsAffected : 0
   
   ,rows :  null
   
   ,init : function(cfg){
        this.rowsAffected = cfg.rowsAffected || 0;
        this.insertId        = cfg.insertId || undefined;
        this.rows            = new SQLResultSetRowList(cfg.data || []);
    }
    
   ,toString : function(){
   
        return "[object SQLResultSet]";
   }

});

/**
@class: SQLTransaction

See: http://www.w3.org/TR/webdatabase/#sqltransaction
*/
SQLTransaction = baseLib.extend({

    _db : null

   ,init : function(db){
        this._db = db;
    }

    /*
        The entry point of all SQL processing.
    */
   ,executeSql : function(sqlStatement, args, successCallback, errorCallback){

        var _exec = function(me, sqlStatement, args, successCallback, errorCallback){

            var isArgNullOrUndef = args === undefined || args === null, typeofArgs;
            
            args = isArgNullOrUndef ? [] : args;
            
            if( !isArgNullOrUndef ){
                typeofArgs = typeof args;
                if( typeofArgs === 'string' || typeofArgs === 'number' ){
                    throw new TypeError('The type of an object was incompatible with the expected type of the parameter associated to the object.');
                }
                
                if( baseLib.isScalar(args) ){
                    args = [undefined];
                }
                else{
                    var _args = [];
                    baseLib.each(args, function(key, val){
                        _args.push(val);
                    });
                    args = _args;
                }                
            }
            
            me.sql = sqlStatement;
            
            var parseTree = null
                ,result = {}
                ,parser = baseLib.sqlParser
				,escapeFn = function(val){
                    return val;
                };

            try{

                //parseTree = parser.parse(parser.lexer.tokenize2(sqlStatement, {args : args, escFn : escapeFn}));
                parseTree = parser.parse2(sqlStatement, {args : args, escFn : escapeFn});
            }
            catch(e){
                if( typeof errorCallback === 'function' ){
                    errorCallback.apply(context, [me,  new SQLError((e.message || e.toString()), _SQLErrorCodes.SYNTAX_ERR)]);
                }
                return;
            }
            
            var drver = me._db.getStorageDriver();
            drver._proc(parseTree, result, successCallback, errorCallback, me);			
            
        };


        _exec(this, sqlStatement, args, successCallback, errorCallback);

    }

   ,insert : function(tableName, record, successCallback, errorCallback){
        
        var
            callbacks = {cbSuccess : successCallback, cbError : errorCallback}
           ,drver = this._db.getStorageDriver()
           ,optimization = (function(drver, tableName, record){
                
                var optimizer = new QueryOptimizer({storageEngine : drver})
                     ,optimization = {newParseTree : {}, executionPlan : []}
                     ,NODES = baseLib.sqlParser.nodes
                     ,pt, insertExp, fields;
                
                insertExp = (function(record, NODES){
                
                    var fName, val, _record = [], _val;
                    
                    for(fName in record){
                        val = record[fName];
                        _val = null;
                        switch(typeof val){
                            case 'string' : {
                                _val =  new NODES.StringValue(val, "'");
                                break;
                            }
                            default : {
                                _val = new NODES.NumberValue(val, val < 0 ? '-' : '+');
                            }                            
                        }
                        _record.push(_val);
                    }
                    
                    return _record;
                    
                }(record, NODES));
                
                fields = (function(record, NODES){
                    var fName, fields = [], field;
                    for(fName in record){
                        field = new NODES.Field(
                            new NODES.LiteralValue(fName)
                           ,new NODES.LiteralValue(fName)
                        );
                        fields = fields.concat([field]);
                    }
                    return fields;
                }(record, NODES));
                
                pt = new NODES.Insert(new NODES.LiteralValue(tableName), insertExp, fields);

                optimizer.generatePlan(pt, optimization);
                
                return optimization;
                
           }(drver, tableName, record));
        
        drver._insertToTable(tableName, optimization, callbacks, this);
        
   }
   
   ,toString : function(){
   
        return "[object SQLTransaction]";
        
   }
   
});

/**
@class: SQLDatabase

See: http://www.w3.org/TR/webdatabase/#database
Note: This class only implements the function "transaction".

*/
SQLDatabase = baseLib.extend({

    _storageDriver : null

   ,init : function(cfg){
        
       var strDriver = cfg.storageDriver;	   
       this.storageDriver = function () {
           return strDriver;
       };

       delete cfg.storageDriver;
       
       baseLib.apply(this, cfg);
       baseLib.apply(cfg, {db : this});
       
       //this._storageDriver = new baseLib[strDriver](cfg);
        
        this._storageDriver = strDriver === "IndexedDB" 
                                        ? new IndexedDB(cfg)
                                        : new LocalStorage(cfg);

    }

   ,transaction : function(transCallback, errorCallback, successCallback){
   
        var db = this;
        this.getStorageDriver().requestTransaction(function(){
            transCallback.apply(context, [new SQLTransaction(db)]);
        });
        
    }

   ,getStorageDriver : function(){
       
        return this._storageDriver;
    }
    
    //performs a manual initialisation of the database
   ,mInit : function(data, success, error){
   
        var storDrver = this.getStorageDriver();
        this.transaction(function(trx){
            storDrver._mInit(data, success, error);
        });
        
   }

});

/**
@class: QueryOptimizer

This class handles optimization concerns, which are the following:
- Expression transformation of boolean expressions
- Pre-evaluation of expressions that does not involve field references
- Generation of execution plan.

Aside from those items above it also handles other items not really related to optimization, but necessary for optimization
to be properly carried out. The following are those items:
- semantic checking (checking for invalid field or table references; ambiguous fields, etc.)
- restructuring of the initial parse tree.
*/
QueryOptimizer = baseLib.extend({

    storageEngine : null
    
   ,_supportedOperators : {} //proxy
   
   ,_aggOperators : {} //proxy
   
   ,subQueries : []
   
    ,init : function(config){
   
        baseLib.apply(this, config);
        
        var storageEngine = this.storageEngine;
        baseLib.apply(this, {
            _supportedOperators : storageEngine.supportedOperators
           ,_aggOperators : storageEngine.aggOperators
        });

    }
    
    ,generatePlan : function(parseTree, result){

        var et = this._buildExpressionTree(parseTree)
            ,NODES = sqlParser.nodes;

        switch(true){
        
            case (parseTree instanceof NODES.QueryPlan) :
            case (parseTree instanceof NODES.SubQuery) :
            case (parseTree instanceof NODES.Select) : {
            
                result.newParseTree = et;
                var unions = et.unions;
                if( !unions ){
                    this._generateSelectExecutionPlan(et, result);
                }
                else{
                    var len = unions.length, _result = {newParseTree : {}, executionPlan : []}, ep = [], union, i;
                    for(i = 0; i < len; i++){
                        union = unions[i];
                        this._generateSelectExecutionPlan(union, _result);
                        ep = ep.concat([_result.executionPlan]);                        
                    }
                    result.executionPlan = ep;
                    result.executionPlanSQ = _result.executionPlanSQ;
                }
                break;
                
            }
        
            case ( parseTree instanceof NODES.Delete ) : {
            
                result.newParseTree = et;
                this._generateDeleteExecutionPlan(et, result);
                break;
            }
            
            case ( parseTree instanceof NODES.Update ) : {
            
                result.newParseTree = et;
                this._generateUpdateExecutionPlan(et, result);
                break;
            }
            
            case (parseTree instanceof NODES.Insert ) : {

                result.newParseTree = et;
                this._generateInsertExecutionPlan(et, result);
                break;
            
            }
            
            default : {
                
                result.newParseTree = parseTree;
                result.executionPlan  = [];
            }
        }
        
    }	
    
    ,_computeEstimatedCost : function(plan, prevTables){
   
        var _plan = baseLib.apply({}, plan)
            ,preCount    = _plan.pre ? _plan.pre.length : 0
            ,numIndex = plan.post ? (function(post, prevTables){
        
                var tbl = '', count = 0;
                for(tbl in prevTables){
                    count += post[tbl] ? post[tbl].length : 0;
                }
                return count;
                
            }(plan.post, prevTables)) : 0;
        
        baseLib.apply(_plan, {
            numIndex : numIndex
           ,estRows   : (_plan.estRows / Math.pow(10, preCount))
           ,physicalOp : ((preCount + numIndex) > 0) ? 'INDEX_SCAN' : 'FULL_SCAN'
        });
        
        return _plan;
    
    }
    
    //returns true if plan2 is "better" than plan1
    ,_comparePlans : function(plan1, plan2){
    
        if( (plan1.actRows > 0 && plan2.actRows > 0) && plan1.actRows < plan2.actRows ){ return false; }
        
        if( plan1.estRows > plan2.estRows ){ return true; }
        
        if( plan1.numIndex < plan2.numIndex ){ return true; }
        
        return false;
        
    }

    ,_generateDeleteExecutionPlan : function(deleteTree, result){
        
        result.executionPlan = [];

        var delCon = deleteTree.deleteCondition;
        if( delCon ){
            var tblNameUC = deleteTree.target.values.join('.').toUpperCase();
            result.executionPlan = [{
                variable : tblNameUC
               ,alias  : tblNameUC
               ,pre : delCon.preCond ? delCon.preCond[tblNameUC] : []
               ,fields : result.newParseTree.fields
            }];			
        }
        
    }
    
    ,_generateUpdateExecutionPlan : function(updateTree, result){
        
        result.executionPlan = [];

        var updCon = updateTree.updateCondition;
        if( updCon ){
            var tblNameUC = updateTree.target.values.join('.').toUpperCase();
            result.executionPlan = [{
                variable : tblNameUC
               ,alias  : tblNameUC
               ,pre : updCon.preCond ? updCon.preCond[tblNameUC] : []
               ,fields : result.newParseTree.fields
            }];			
        }
        

    }
    
    ,_generateInsertExecutionPlan : function(insertTree, result){
    
        result.executionPlan = [];
        
    }
    
    ,_generateSelectExecutionPlan : function(selectTree, result){
    
        var initialJoinPlan	= this._generateJoinInitialPlan(selectTree)
             ,numOfJoins		= initialJoinPlan.length
             ,finalJoinPlan	= []
             ,prevTables		= {};

        /*
        if number of joins involved is greater than 1
        then, attempt at a reordering of the JOINed tables.
        */
        if( numOfJoins > 1 ){
        
            var i, len, j, plan, alias, isLeftJoin, _cp, chosenPlan;
            for(i = 0, len = numOfJoins; i < len; i++){
            
                chosenPlan = null;
                
                for(j = 0; j < len; j++){
                    
                    plan = initialJoinPlan[j];
                    alias = plan.alias;
                    
                    if( prevTables[alias] ){
                        continue;
                    }
                    
                    isLeftJoin = (plan.operator === 'OUTERJOIN');
                    _cp = this._computeEstimatedCost(plan, prevTables);
                    
                    if( isLeftJoin && chosenPlan ){
                        continue;
                    }
                    
                    if( !chosenPlan || this._comparePlans(chosenPlan, _cp) ){					
                        chosenPlan = _cp;
                    }
                    
                }
            
                finalJoinPlan = finalJoinPlan.concat([chosenPlan]);
                prevTables[chosenPlan.alias] = plan.fields;
                
            }

        }
        else{
            //the initial join plan is final.
            finalJoinPlan = initialJoinPlan;
        }
        
        if( finalJoinPlan.length !== numOfJoins ){
        
            var msg = "Inconsistency when generating optimal execution plan. "
                            + "Orig. # of joins: " + numOfJoins
                            + "Planned # of joins: " + finalJoinPlan.length + ".";
                            
            throw new SQLError(msg, _SQLErrorCodes.DATABASE_ERR);
        
        }
        
        result.executionPlan = finalJoinPlan;
        result.executionPlanSQ = this.subQueries;
                
        return finalJoinPlan;
    
    }
    
    ,_generateJoinInitialPlan : function(selectTree){
    
        var joins = selectTree.joins
             ,jLen   = joins.length
             ,where = selectTree.where
             ,preCond = where.preCond
             ,postCond = where.postCond
             ,iPlan   = []
             ,prevTables = {}
             ,i, join, alias, plan, pre, meta;

        for(i = 0; i < jLen; i++){
        
            join		= joins[i];
            alias		= join.alias;
            plan		= baseLib.apply({}, join);
            pre		= preCond[alias];
            meta	= join.meta ? join.meta[join.alias] : {};
            
            baseLib.apply(plan, {
                pre : pre
               ,post : postCond[alias]
               ,actRows : meta.numOfRows || -1
               ,estRows : join.data ?  join.data.length : 1000000
               ,physicalOp  : pre && pre.length > 0 ? 'INDEX_SCAN' : 'FULL_SCAN'
               ,numIndex : 0
            });
            
            iPlan = iPlan.concat([plan]);
            
            prevTables[plan.alias] = true;
            
        }
        
        
        return iPlan;
        
    }
    
    ,_buildExpressionTree : function(parseTree){
        
        var newParseTree = {}
             ,NODES = sqlParser.nodes;

        switch(true){
            
            case parseTree instanceof NODES.SubQuery :
            case parseTree instanceof NODES.Select : {
                
                newParseTree = this._buildSelectTree(parseTree);
                
                return newParseTree;
            }
            
            case parseTree instanceof NODES.QueryPlan : {
                
                newParseTree = this._buildSelectTree(parseTree.query);				
                return newParseTree;
            }
            
            case parseTree instanceof NODES.Delete : {
                
                newParseTree = this._buildDeleteTree(parseTree);
                return newParseTree;
            }
            
            case parseTree instanceof NODES.Update : {
                
                newParseTree = this._buildUpdateTree(parseTree);
                return newParseTree;
            }
            
            case parseTree instanceof NODES.Insert : {
                newParseTree = this._buildInsertTree(parseTree);
                return parseTree;
            }
            
            default : {
                return parseTree;
            }
        }
        
    }
    
    ,_buildSelectTree : function(selectTree, isSubSelect){
    
        //if the "from" clause is a sub-selection
        isSubSelect = isSubSelect || false;

        //just a shorthand. for faster access.
        var _getTableReference = this._getTableReference
             ,OP							= sqlParser.nodes.Op;
        
        /*
        treat all SELECT operations as JOINs. A SELECT with just one table is a JOIN with just one table.
        this approach allows us to use the optimizations applied to multi-table queries even for single-table queries.
        
        we immediately get the expression(e.g., subselect, table reference) in the FROM clause...
        
        the first item in the set of joins is an INNER JOIN since, we can always add another item before the first
        during the optimization process.
        */		
        var joinResult	= _getTableReference.apply(this, [selectTree.source])
             ,allJoinFields = baseLib.apply({}, joinResult.fields)
             ,allJoinMeta	= baseLib.apply({}, joinResult.meta)
             ,userJoins	= selectTree.joins
             ,ujLen			= userJoins.length
             ,joins			= [baseLib.apply(joinResult, {from : 0, operator : 'INNERJOIN'})]
             
             /*
             we do this to preserve the original WHERE clause in the original parse tree
             what might be updated, though, are the individual terms. Node StringValue will be changed to the native string.
             */
             ,owhere =  selectTree.where //orig where
             ,ocond   = owhere ? owhere.conditions : true //orig condition
             
             ,where = {conditions : !(ocond instanceof OP) ? ocond : new OP(ocond.operation, ocond.left, ocond.right)}
             ,join, ujIndex, hasOuterJoin = false;

        //if a multi-table SELECT .... then just process appropriately.
        if( ujLen > 0 ){
            var r, c, m;//, op;
            for(ujIndex = 0; ujIndex < ujLen; ujIndex++){
            
                join	= userJoins[ujIndex];
                r		= _getTableReference.apply(this, [join.right]);
                c		= join.conditions;
                m     = join.mode;

                hasOuterJoin = hasOuterJoin || (m === 'OUTER');
                                
                //add the join conditions to the where condition
                if( c ){
                    c.isJoinCondition = true;
                    where.conditions = new OP('and', where.conditions, c);
                }
                
                baseLib.apply(r , {operator : m+'JOIN', from : ujIndex+1});
            
                this._computeJoinFieldsandMeta(r, allJoinFields, allJoinMeta);

                joins = joins.concat([r]);

            }
        
        }

        joinResult = {
            fields	 : allJoinFields
           ,meta : allJoinMeta
        };

        var selectBlock = {fields : joinResult.fields, joins : null, where : null, select : {}, order : null, group : null, limit : null, meta : {}};
        
        /*
        the actual assumption that all SELECTs are JOINs ... it won't matter whether one or many table
        was involved in the query.
        */
        selectBlock.joins = joins;
        
        //the condition is not a native BOOLEAN. hence, we need to do possible optimization
        if( typeof where.conditions !== 'boolean' ){

            var fields = joinResult.fields;

            this._checkIfValidExpr(where.conditions, selectBlock, true);
            this._preEvaluateExpression(where, selectBlock);
            this._transformBooleanExpressions(where);
            this._splitBooleanExprByAnd(where, fields, hasOuterJoin, joins);			
            
        }

        selectBlock.where = baseLib.apply({preCond : {}, postCond : {}, otherCond : {}}, where);

        var preSelectFields = joinResult.fields; //will be used during GROUP or ORDER (if there is)
        
        var parts = [
            'FIELDS' //-->represents SELECT
           ,'GROUP'
           ,'ORDER'
           ,'LIMIT'
        ]
        ,i		     = 0
        ,currPart = ''
        ,len	      = parts.length
        ,currPartLC, userFields, _result, _group;
        
        while( i < len ){
                                
            currPart = parts[i];
            i++;
            
            currPartLC = currPart.toLowerCase();
            
            if( !selectTree[currPartLC] ){
                continue;
            }
            
            switch(currPart){
                
                case 'FIELDS' : {

                    userFields	= selectTree.fields;
                    _result		= this._buildSelectFieldList(userFields, joinResult.fields, joinResult.meta, isSubSelect, selectBlock);

                    baseLib.apply(selectBlock.select, {
                        distinct : selectTree.distinct || false
                       ,userFields : userFields
                       ,finalFields : _result.fields
                       ,aliasedFields : _result.aliasMapping
                    });

                    baseLib.apply(selectBlock.meta, {
                        numFields  : _result.numFields
                    });
                    
                    break;
                    
                }
                
                case 'GROUP' : {
                    
                    _group = selectTree[currPartLC];
                    
                    selectBlock.group = {
                        groupings : this._buildOrderByOrGroupByList(_group.fields, selectBlock, preSelectFields, 'group')
                    };
                    
                    if(_group.having ){
                        baseLib.apply(selectBlock, {having : _group.having});
                    }
                    
                    
                    break;
                    
                }

                case 'ORDER' : {
                
                    selectBlock.order = this._buildOrderByOrGroupByList(selectTree[currPartLC].orderings, selectBlock, preSelectFields, 'order');
                    break;
                    
                }
                
                case 'LIMIT' : {
                
                    selectBlock.limit = selectTree[currPartLC];				
                    
                    break;
                    
                }
            
            }
        
        }
    
        baseLib.apply(selectBlock, {meta : baseLib.apply(selectBlock.meta, joinResult.meta)});
        
        /*
            keep note of those fields that will end up to be joined in a tuple.
        */
        var allJoins  = selectBlock.joins, joinFields, fKey, f;
        for(ujIndex = 0, ujLen = allJoins.length; ujIndex < ujLen; ujIndex++){
            
            join = joins[ujIndex];
            joinFields = join.fields;
            
            join.jFields = [];
            
            for(fKey in joinFields){			
                f = joinFields[fKey];
                //do not include ambiguous fields, or those fields that are qualified but whose mapping did not change.
                if( !(f.isAmbiguous || (f.isQualified && f.mapping === f.mappingNew)) ){
                    join.jFields = join.jFields.concat([f]);
                }
            }
            
        }
        
        //take note of the expression fields and build an array of final fields
        var _finalFields = selectBlock.select.finalFields, expFields = [], aggExpFields = [], aFinalFields = [], key, expr;
        for(key in _finalFields){
            f = _finalFields[key];
            expr = f.expr;
            if( expr !== undefined ){
                if(  typeof expr === 'object' && expr !== null && true === expr.hasAggOp ){
                    aggExpFields = aggExpFields.concat([f]);
                }
                else{
                    expFields = expFields.concat([f]);
                }
            }
            
            aFinalFields = aFinalFields.concat([f]);
            
        }
        
        var _grpFields = selectBlock.group ? (selectBlock.group.groupings || []): [];
        for(i = 0, len = _grpFields.length; i < len; i++){
            f = _grpFields[i];
            if( f.expr !== undefined ){
                expFields = expFields.concat([{expr : f.expr.node, isGroupingExpr : true, groupIndex : i}]);
            }
        }        
        
        baseLib.apply(selectBlock.select, {
            //combine aliased fields and all the (joined) fields, so that expressions in group by that refers to aliased fields
            //can be properly evaluated.
            allFields : baseLib.apply(baseLib.apply({}, selectBlock.fields), selectBlock.select.aliasedFields)
           ,aFinalFields : aFinalFields
           ,expFields : expFields
           ,aggExpFields : aggExpFields
        });

        var unions = selectTree.unions
            ,_unions = []
            ,unLen	= unions.length
            ,idxUnion = 0
            ,isUnionAll = false
            ,numFields = selectBlock.meta && selectBlock.meta.numFields ? selectBlock.meta.numFields : 0
            ,u, select;
            
        if( unLen > 0 ){
            
            _unions = [baseLib.apply(selectBlock, {isUnionAll : false})];            
            
            for(idxUnion = 0; idxUnion < unLen; idxUnion++){
            
                u 		= unions[idxUnion];
                select = this._buildSelectTree(u.query, isSubSelect);
                isUnionAll = u.all || false;
                if( select.meta && select.meta.numFields !== numFields ){
                   throw new SQLError("could not prepare statement (1 SELECTs to the left and right of UNION" + (isUnionAll ? " ALL " : " ") + "do not have the same number of result columns)", _SQLErrorCodes.SYNTAX_ERR);
                }
               _unions = _unions.concat([baseLib.apply(select, {isUnionAll : isUnionAll})]);
               
            }
            
            return {
                unions : _unions
            };
            
        }

        
        
        return selectBlock;
        
    }

    ,_buildDeleteTree : function(deleteTree){
    
        var NODES	= sqlParser.nodes
             ,select		= new NODES.Select(
                    new NODES.Star()
                   ,new NODES.Table(
                        new NODES.LiteralValue(deleteTree.target.values.join('.'))
                    )
                )
              ,where	= deleteTree.deleteCondition;

        
        select = this._buildSelectTree(select);
        
        if( where ){
        
            this._checkIfValidExpr(where.conditions, select);
            
            this._preEvaluateExpression(where);
            
            this._transformBooleanExpressions(where);
            
            this._splitBooleanExprByAnd(where, select.fields);

        }
        
        return baseLib.apply(deleteTree, {
            fields : select.fields
        });
        
    }
    
    ,_buildUpdateTree : function(updateTree){
    
        var NODES	= sqlParser.nodes
             ,select		= new NODES.Select(
                    new NODES.Star()
                   ,new NODES.Table(
                        new NODES.LiteralValue(updateTree.target.values.join('.'))
                    )
                )
              ,where	= updateTree.updateCondition;

        
        select = this._buildSelectTree(select);
        
        var assignmentList	= updateTree.assignList
             ,j, jLen, op, expr;

        for(j = 0, jLen = assignmentList.length; j < jLen; j++){
            
            op = assignmentList[j];
                
            if( op.operation !== '=' || !(op instanceof NODES.Op) || !(op.left instanceof NODES.LiteralValue ) ) {
                throw new SQLError("Invalid assignment in SET clause.");
            }			
            
            this._checkIfValidExpr(op.left, select);
            
            this._checkIfValidExpr(op.right, select);
            
            expr = {conditions : op.right};
            
            this._preEvaluateExpression(expr);
            
            this._transformBooleanExpressions(expr);
            
            op.right = expr.conditions;
        }		
        
        if( where ){
        
            this._checkIfValidExpr(where.conditions, select);
            
            this._preEvaluateExpression(where);
            
            this._transformBooleanExpressions(where);
            
            this._splitBooleanExprByAnd(where, select.fields);

        }
        
        return baseLib.apply(updateTree, {
            fields : select.fields
        });
        
    }
    
    ,_buildInsertTree : function(insertTree){
    
        var NODES				= sqlParser.nodes
             ,tbl						= this._getTableReference(new NODES.Table(insertTree.target))
             ,insertFields			= insertTree.fields || []
             ,insertFieldsLen	= insertFields.length
             ,i, pt;
        
        //examine fields
        for(i = 0; i < insertFieldsLen; i++){		
            this._checkIfValidExpr(insertFields[i].name, tbl);
        }
        
        var insertExp 				= insertTree.insertExpression
             ,insertExpIsSelect		= insertExp instanceof NODES.Select
             ,insertExpFldCount	= 0;
        
        
        insertFieldsLen = insertFieldsLen === 0 ? (function(fields){
            var count = 0, key;
            for(key in fields) {
                if( fields[key].isQualified ){
                    continue;
                }
                count++;
            }
            return count;
        }(tbl.fields)) : insertFieldsLen;
        
        if( insertExpIsSelect ){		
            pt 			= this._buildSelectTree(insertExp, true);
            insertExpFldCount = baseLib.count(pt.select.finalFields);
        }
        else{
            insertExpFldCount = baseLib.count(insertExp);
        }
        
        //check if the number of fields corresponds to the number of values
        if( insertExpFldCount !== insertFieldsLen ){
            throw new SQLError("could not prepare statement (1 " + insertExpFldCount + " values for " + insertFieldsLen + " columns)", _SQLErrorCodes.SYNTAX_ERR);
        }
        
        if( insertExpIsSelect && pt){
            var result = {};
            this._generateSelectExecutionPlan(pt, result);
            insertExp.ep = result.executionPlan;
            insertExp.pt  = pt;
        }
        
        return insertTree;

    }
    
    ,_computeJoinFieldsandMeta : function(relation, allJoinFields, allJoinMeta){
            
            var fields = relation.fields
                 ,aliasUC = relation.alias || ''
                 ,isCorrelated = relation.isCorrelated || false
                 ,fKey = ''
                 ,fIndex = []
                 ,currFld, prevFld, prevFldQlfd, currFldQlfd;

            for(fKey in fields){
            
                currFld = fields[fKey]; //current field
                prevFld = allJoinFields[fKey]; //previous field
                
                //meaning there was a duplicate...
                if( prevFld ) {
                    
                    if( isCorrelated ){
                        continue;
                    }
                    
                    //try to get the qualified version of the previous field that has a duplicate.
                    //convert that qualified version to a valid field. the "mappingNew" attribute
                    //will be used in JOINing the row later on.
                    prevFldQlfd = allJoinFields[(prevFld.aliasSourceUC || '') + '.' + prevFld.name];
                    if(  prevFldQlfd ){
                    
                        prevFldQlfd = baseLib.apply(prevFldQlfd, {
                            mapping : prevFld.mapping
                           ,mappingNew : prevFldQlfd.aliasSourceUC + '.' + prevFldQlfd.mappedField
                           ,nameOrig : prevFld.nameOrig
                        });
                        
                    }
                    
                    //do the same for the current field
                    currFldQlfd = fields[(currFld.aliasSourceUC || '') + '.' + currFld.name];
                    if(  currFldQlfd ){
                    
                        currFldQlfd = baseLib.apply(currFldQlfd, {
                            mapping : currFld.mapping
                           ,mappingNew : currFldQlfd.aliasSourceUC + '.' + currFldQlfd.mappedField
                           ,nameOrig : currFld.nameOrig
                        });
                        
                    }
                    
                    //now tag both fields as ambiguous. the converted qualified field counterparts will be registered later on.
                    //if they are still duplicated, then they will be tagged as ambiguous, too.
                    baseLib.apply(prevFld, {isAmbiguous : true});
                    baseLib.apply(currFld, {isAmbiguous : true});
                    
                    continue;
                }
                
                allJoinFields[fKey] = currFld;
                
                fIndex = fIndex.concat([fKey]); 

            }

            if( aliasUC && !allJoinMeta[aliasUC] && relation.meta && relation.meta[aliasUC] ){
                allJoinMeta[aliasUC] = baseLib.apply(relation.meta[aliasUC], {
                    fIndex : fIndex
                });
            }

    }

    /**
    method that will check if a sub-expression in a relational/select expression is valid
    i.e., if it references a valid field, if an operator's use is allowed in a given context (i.e., aggregate operators)
    **/
    ,_checkIfValidExpr : function(exp, parseTree, disAllowAggOp, isInGroupBy, result){

        result				= result || {};
        disAllowAggOp	= disAllowAggOp || false; //whether we are in the middle of an aggregate operator
        isInGroupBy		= isInGroupBy || false;
        
        var NODES		= sqlParser.nodes
            ,expArgs		= [] // expression arguments	
            ,op				= '';//if this remains blank, meaning it's a literal or attr. reference
            
        switch(true){
        
            case ( exp instanceof NODES.Op ) : {
                
                op = exp.operation;
            
                expArgs = [exp.left, exp.right];
                
                break;
                
            }
            
            case ( exp instanceof NODES.FunctionValue ) : {
                op        = exp.name;
                expArgs = exp.arguments;
                
                break;
            }
            
            case ( exp instanceof NODES.FunctionCastValue ) : {
                op = exp.name;
                expArgs = [exp.expression, exp.toType.value];
                break;
            }
            
            case ( exp instanceof NODES.SubQuery ) : {				
                this._examineSubQuery(exp, parseTree);
                return;
            }
            
        }		
        
        if( op === '' ){
        
            //an attribute reference was encountered
            if( exp instanceof NODES.LiteralValue ){
                
                var name 		= exp.values.join(".")
                     ,nameUC = name.toUpperCase()
                     ,aliasedFields = parseTree.select ? parseTree.select.aliasedFields : undefined
                     ,f 	   		= parseTree.fields[nameUC];

                if( aliasedFields && !f ){
                    f = aliasedFields[nameUC];
                }                
                
                this._checkIfValidField(f, name);

            }
        
            return;
        
        }

        var opUC = op.toUpperCase();
        
        if( this._supportedOperators[opUC] === undefined ){
            throw new SQLError("Operator/function '" + op + "' is not yet supported.");
        }

        if( this._aggOperators[opUC] ){
        
            if( disAllowAggOp ){
            
                if( isInGroupBy ){
                    throw new SQLError("could not prepare statement (1 aggregate functions are not allowed in the GROUP BY clause)", _SQLErrorCodes.SYNTAX_ERROR);
                }
                
                throw new SQLError("could not prepare statement (1 misuse of aggregate function " + op + ")", _SQLErrorCodes.SYNTAX_ERR);
                
            }
            
            disAllowAggOp = true;
            
        }
        
        var i, len, _result;
        for(i = 0, len = expArgs.length; i < len; i++){
            _result = {};
            this._checkIfValidExpr(expArgs[i], parseTree, disAllowAggOp, isInGroupBy, _result);
        }

    }
    
    ,_checkIfValidField : function(f, name){
        
        if( !f ){
            throw new SQLError("could not prepare statement (1 no such column: " + name + ")", _SQLErrorCodes.SYNTAX_ERR);
        }

        if( f.isQualified === true ){
            if( f.isAmbiguous === true ){
                throw new SQLError("could not prepare statement (1 ambiguous column name: " + name + ")", _SQLErrorCodes.SYNATX_ERROR);
            }
            return;
        }
        
        if( f.isAmbiguous ){
            throw new SQLError("could not prepare statement (1 ambiguous column name: " + name + ")", _SQLErrorCodes.SYNTAX_ERR);
        }
   
    }

   /*
   Examine the subquery if it could be executed early.
   If so, the "isCorrelated" attribute of the subquery will be set to false; otherwise, it would be true.
   In either case, the subquery will have two new attributes: ep and pt which contains the execution plan and new parse tree
   for the subquery, respectively.
   */
   ,_examineSubQuery : function(subQuery, parseTree){
        
        var _generatePlan = function(subQuery, isCorrelated, result){
        
            result.error = null;
            
            var _result	= {};
                 
            try{
                this.generatePlan(subQuery, _result);
                baseLib.apply(subQuery, {
                    ep : _result.executionPlan
                   ,pt : _result.newParseTree
                   ,isCorrelated : isCorrelated
                });
            }
            catch(ex){
                result.error = ex;
            }
            
        };
        
        var result = {};
        
        _generatePlan.apply(this, [subQuery, false, result]);
        
        if( !result.error ){
            return;
        }
        
        var JOIN = sqlParser.nodes.Join			 
             ,sJoins = parseTree.joins
             ,i, len, corr, join;
        
        for(i = 0, len = sJoins.length; i < len; i++){
            
            corr = sJoins[i];
            join  = new JOIN({data : [{}], fields : corr.fields, isCorrelated : true, alias : corr.alias}, true, 'LEFT', 'INNER');
            /*
            Add the correlated tables to the last joins so
            that the original fields of the subquery will be preserved.
            */
            subQuery.joins = subQuery.joins.concat([join]);
        }
        
        _generatePlan.apply(this, [subQuery, true, result]);
        
        if( result.error ){
            throw result.error;
        }
        
   
   }
   
   ,_preEvaluateExpression : function(where, parseTree){
   
        var _fn  = function(exp, parent, level, prevOp, parseTree, NODES){
            
            parseTree           = parseTree || {};
            parseTree.select = parseTree.select || {};
            
            var expArgs		= []
                 ,op 				= ''
                 ,isSubQuery	= false
                 ,v, _v, i, len, opUC;

            var prevOpUC = prevOp ? prevOp.toUpperCase() : '';
            
            switch(true){
            
                case ( exp instanceof NODES.Op ) : {
                    op 		 = exp.operation;
                    expArgs = [exp.left, exp.right];
                    break;
                }
                
                case ( exp instanceof NODES.FunctionValue ) : {
                    op        = exp.name;
                    expArgs = exp.arguments;				
                    break;
                }

                case ( exp instanceof NODES.FunctionCastValue ) : {
                    op = exp.name;
                    expArgs = [exp.expression, exp.toType.value];
                    break;
                }
                
                case ( exp instanceof NODES.SubQuery ) : {
                    
                    isSubQuery		= true;
                    this.subQueries	= this.subQueries.concat([exp]);
                    
                    if( exp.isCorrelated ){
                        v = exp;
                        break;
                    }
                    
                    expArgs = [exp.ep, exp.pt, prevOpUC === 'IN' || prevOpUC === 'NOT_IN'];
                    op = '_procSubQuery';
                    break;
                    
                }
                
                case ( exp instanceof NODES.LiteralValue ) : {
                    v = exp;
                    break;
                }			
                
                //Number, String, Boolean
                default : {
                
                    if( exp === null || exp === undefined ){
                        v = exp;
                        break;
                    }
                    
                    if( baseLib.isArray(exp) === false ){
                        v = exp.value === undefined ? exp : exp.value;
                        break;
                    }
                    
                    var isInOp = (prevOpUC === 'IN' || prevOpUC === 'NOT_IN'),
                         _set = isInOp ? {} : [];

                    for(i  = 0, len = exp.length; i < len; i++){
                        
                        _v = _fn.apply(this, [exp[i], parent, level, op, parseTree, NODES]);
                        
                        if( isInOp ){
                            _set[_v] = true;
                            continue;
                        }
                        
                        _set.push(_v);
                        
                    }
                    
                    v = _set;
                
                }

            }
            
            opUC = op.toUpperCase();

            if(
                      opUC === 'CASE_CONDITION' 
                && parent instanceof NODES.FunctionValue
                && parent.name.toUpperCase() === 'CASE'
            ){
                //add the baseExpression, and isPreEval arguments to the CASE_CONDITION function
                expArgs.push(parent.arguments[1]);
                expArgs.push(true);
            }
            
            ++level;
            
            var _expArgs = [],
                 //if whether or not the operation involves at least 1 non-literal.
                nonLiteral = false,
                _isNonLiteralFn = function(v){
                    
                    v = baseLib.isArray(v) ? v : [v];
                    
                    var i, len = v.length, _v, isNonLiteral = false;
                    
                    for(i = 0; i < len; i++){
                        
                        _v = v[i];
                        
                        isNonLiteral = (
                            _v instanceof NODES.Op ||
                            _v instanceof NODES.FunctionValue ||
                            _v instanceof NODES.FunctionCastValue ||
                            _v instanceof NODES.LiteralValue || 
                            (_v instanceof NODES.SubQuery && v.isCorrelated) ||
                            _v === '*'
                        );
                        
                        if( isNonLiteral ){
                            return true;
                        }
                        
                    }
                    
                    return isNonLiteral;
                    
                };
            
            for(i = 0, len = expArgs.length; i < len; i++){

                _v = _fn.apply(this, [expArgs[i], exp, level, op, parseTree, NODES]);

                nonLiteral = nonLiteral || _isNonLiteralFn(_v);
                
                if( exp instanceof NODES.Op ){
                    exp[i === 0 ? 'left' : 'right'] = _v;
                }
                else if (exp instanceof NODES.FunctionValue || exp instanceof NODES.FunctionCastValue ){
                    expArgs[i] = _v;
                }
                
                _expArgs = _expArgs.concat([_v]);

            }            
            
            if( opUC && this._aggOperators[opUC] ){
                parseTree.select.hasAggOperator = true;
                
            }
            
            if( parseTree.select.hasAggOperator ){
                exp.hasAggOp = true;
            }
            
            if( nonLiteral ){
                v = exp;
            }
            else{
            
                if( opUC ){
                    var se = this.storageEngine;

                     v  = isSubQuery ?
                            se[op].apply(se, _expArgs ) : 
                            se.supportedOperators[opUC].apply(se, _expArgs);
                }
            }
            
            if(level === 1){
                parent.conditions = v;
            }
            
            return v;

        };
        
        parseTree = parseTree || {};
        
        _fn.apply(this, [where.conditions, where, 0, '', parseTree, sqlParser.nodes]);
        
   }
   
   ,_transformBooleanExpressions : function(where){
   
        var _fn  = function(exp, parent, level, NODES){
        
            if( !(exp instanceof NODES.Op ) ){
                return exp;
            }
            
            
            var booleanOp = {
                    'AND' : true,
                    'OR' : true
                 }
                 ,op = exp.operation.toUpperCase();
            
            if( !booleanOp[op] ){
                return exp;
            }

            ++level;
            
            var expArgs = [exp.left, exp.right], hasBoolean = false, i, len, _v;
            for(i = 0, len = expArgs.length; i < len; i++){
                _v = _fn.apply(this, [expArgs[i], parent, level, NODES]);
                hasBoolean = hasBoolean || (typeof _v === 'boolean');
                exp[ i === 0 ? 'left' : 'right'] = _v;
            }
            
            if( !hasBoolean ){
                return exp;
            }
            
            var 
                bSide	= typeof exp.left === 'boolean' ? 'left' : 'right'
               ,nbSide	=  bSide === 'left' ? 'right' : 'left'
               ,bVal		= exp[bSide]
               ,v;

            /**
                T && <e> == <e>
                F && <e> == F
                
                T || <e> == T
                F || <e> == <e>				
            **/
            
            if( op === 'AND' ){
                v = bVal === false ? false : exp[nbSide];
            }
            else{
                v = bVal || exp[nbSide];
            }
            
            if(level === 1){
                parent.conditions = v;
            }
            
            return v;
            
        };
        
        _fn.apply(this, [where.conditions, where, 0, sqlParser.nodes]);

   }
   
   ,_splitBooleanExprByAnd  : function(where, fields, hasOuterJoin, joins){
   
        //whether or not the main expression that the boolean/where expression belongs involves an OUTER JOIN.
        //important to properly classify each terms in the boolean/where expression.
        hasOuterJoin = hasOuterJoin || false;
        joins = joins || [];
        
        //returns TRUE if the field reference is from a table that is OUTER JOINed
        var isFieldFromOuterJoin = function(fieldReference, joins){
        
            var name = fieldReference.values.join('.').toUpperCase()
                 ,table  = fields[name] ? fields[name].aliasSourceUC : ''
                 ,join, i, len = joins.length;
            
            if( table ){                          
            
                for(i = 0; i < len; i++){
                    join = joins[i];
                    if( join.alias === table ){
                        if( join.operator === "OUTERJOIN" ){
                            return true;
                        }
                        break;
                    }
                }
                
            }
            
            return false;
            
        };
        
        var _split  = function(exp, terms, NODES){					
            
            var isJoinCondition = exp.isJoinCondition || false; //take note if parent term is a join condition or note.
            
            if( !(exp instanceof NODES.Op) || exp.operation.toUpperCase() !== 'AND' ){
            
                terms = terms.concat(exp);
                
            }
            else{
            
                var left = exp.left, right = exp.right;
                
                //if child condition is not yet a join condition, then use the join condition status of the parent.
                left.isJoinCondition = left.isJoinCondition || isJoinCondition;
                right.isJoinCondition = right.isJoinCondition || isJoinCondition;
                
                terms = _split.apply(this, [left, terms, NODES]);                                
                terms = _split.apply(this, [right, terms, NODES]);
                
            }
            
            return terms;
            
        };

        var regCond = function(fields, field, refFieldOrVal, op, term, condList){
            
            var name = field.values.join('.').toUpperCase()
                 ,table  = fields[name].aliasSourceUC
                 ,cond;
            
            
            if( typeof refFieldOrVal !== 'object' || refFieldOrVal === null ){
                condList[table] = condList[table] || [];
                cond = condList[table];
                condList[table] = cond.concat([{field  : name,  op  : op, val : refFieldOrVal, term : term}]);
                return;
            }
            
            var refName = refFieldOrVal.values.join('.').toUpperCase()			    	 
                 ,refTable  = fields[refName].aliasSourceUC;

            condList[table] = condList[table] || {};	 
            cond = condList[table][refTable] = condList[table][refTable] ||  [];			
            condList[table][refTable]  = cond.concat([{field  : name,  op  : op, refField : refName, term : term}]);
            
        };

        var NODES		= sqlParser.nodes
             ,LITVAL		= sqlParser.nodes.LiteralValue
             ,terms			= _split.apply(this, [where.conditions, [], NODES])
             ,operators	= ['=', '==', '<', '>', '<=', '>=', '<>', '!=', 'IS', 'IS NOT']
             ,preCond		= {}
             ,postCond	= {}
             ,otherCond	= []
             ,i, len, left, right, fldRef, other, op, term, isJoinCondition;
        
        //analyse each splitted term
        for(i = 0, len = terms.length; i < len; i++){
        
            term = terms[i];
            isJoinCondition = term.isJoinCondition || false;
            
            switch(true){
            
                case (term instanceof NODES.Op) && operators.indexOf(term.operation.toUpperCase()) !== -1 : {
                
                    left		= term.left;
                    right		= term.right;
                    fldRef	= left instanceof LITVAL ? left : (right instanceof LITVAL ? right : null);
                    other	= fldRef === null ? null : (fldRef === left ? right : left);
                    op		= term.operation;
                    
                    /*
                    transfer a condition in "other conditions" if the following are all true:
                        1.) the expression involves an OUTER JOIN
                        2.) the condition in question is not a JOIN condition (i.e., it is a WHERE condition)
                        3.) the condition in question involves one literal, and one field reference.
                        4.) the field reference involved in the condition is from a table that is OUTER JOINed.
                    */
                    if(
                                hasOuterJoin === true 
                        &&   isJoinCondition === false 
                        &&   (fldRef && (typeof other !== 'object' || other === null))
                        &&   isFieldFromOuterJoin(fldRef, joins) === true
                    ){
                        otherCond = otherCond.concat([term]);
                        break;
                    }
                    
                    switch(true){

                        //if both are field references
                        case fldRef && (other instanceof LITVAL) : {
                            regCond.apply(this, [fields, fldRef, other, op, term, postCond]);
                            regCond.apply(this, [fields, other, fldRef, op, term, postCond]);
                            break;
                        }

                        //one is a field reference, and the other is a constant
                        case fldRef && (typeof other !== 'object' || other === null): {
                            regCond.apply(this, [fields, fldRef, other, op, term, preCond]);
                            break;
                        }
                        
                        default : {
                            otherCond = otherCond.concat([term]);
                        }
                        
                    }
                     
                    break;
                    
                }

                default  : {
                    otherCond = otherCond.concat([term]);
                }
            
            }

        }
        
    
        baseLib.apply(where, {
            preCond : preCond
           ,postCond : postCond
           ,otherCond : otherCond
        });
   
   }

   ,_buildOrderByOrGroupByList : function(list, selectBlock, preSelectFields, type){
   
        var NODES			= sqlParser.nodes
             ,select				= selectBlock.select
             ,aliasedFields	= select.aliasedFields
             ,items				= []
             ,_type				= type.toUpperCase()
             ,i, len, item, _numFields, userField, _item
             ,name, nameUC, field;
        
        for(i = 0, len = list.length; i < len; i++){
        
            item = list[i];
            
            if( item instanceof NODES.NumberValue ) {
                
                _numFields = selectBlock.meta.numFields;
                
                if( userField.value < 1 || userField.value > _numFields ){
                    throw new SQLError("could not prepare statement (1 " + (i+1) + " " + _type + " BY term out of range - should be between 1 and " + _numFields + ")", _SQLErrorCodes.SYNTAX_ERR);
                }
                
                //@todo
                throw new SQLError(_type + " using field/column index not yet supported.", _SQLErrorCodes.SYNTAX_ERR);
            }
            
            userField = _type === 'ORDER' ? item.value : item; //if no "value" attribute, them the item itself is the subject.
                 
            switch(true){

                case userField instanceof NODES.Op :
                case userField instanceof NODES.FunctionValue :
                case userField instanceof NODES.FunctionCastValue : {
                    
                    if( _type === 'GROUP' ){
                        this._checkIfValidExpr(userField, selectBlock, true, true);
                    }
                    else{
                        this._checkIfValidExpr(userField, selectBlock, !select.distinct && !select.hasAggregate && !selectBlock.group, false);
                    }
                    
                    _item = {
                        expr :  {
                            node : userField
                       }
                    };
                    
                    if( _type === 'ORDER' ){
                    
                        baseLib.apply(_item.expr, {
                            evaluator : this.storageEngine._evalExpInRel
                           ,fields : baseLib.apply(aliasedFields, preSelectFields)
                           ,scope : this.storageEngine
                        });
                    
                    }
                    
                    break;
                
                }
                
                default : {
                    
                    this._checkIfValidExpr(userField, selectBlock);
                    
                    
                    name		= userField.values.join('.');
                    nameUC	= name.toUpperCase();
                    field			= aliasedFields[nameUC]  || preSelectFields[nameUC];
                        
                    _item = {
                        mapping  : field.mappingNew || field.nameDisplay.toUpperCase()
                       ,field  : field
                    };
                
                }

            }
            
            if( _type === 'ORDER' ){
            
                 baseLib.apply(_item, {
                      nocase :  typeof item.collation === 'string' && item.collation.toUpperCase() === 'NOCASE'
                     ,direction : item.direction ? item.direction.toUpperCase() : ''
                 });

            }

            items = items.concat(_item);            
        
        }
        
        return items;
        
   }
   
   ,_buildSelectFieldList : function(selectFields, joinFields, joinMeta, isSubSelect, selectBlock){

        var NODES			= sqlParser.nodes
             ,_finalFields		= {}
             ,_fieldKeys		= {}
             ,_aliasMapping	= {}
             ,_numFields		= 0;
                            
        //function that will register a given field in the final set fields of the select operation
        var _regAsFinalField = function(fld, name, field, _finalFields, _fieldKeys, _aliasMapping, NODES){
                                
            //get the name given to the field being referred to.
            var fldNameDisplay = !name ?
                    fld.nameDisplay :
                    (function(fld, name, isSubSelect, NODES){
                    
                        return name instanceof NODES.LiteralValue ?
                                //if field being referred to is from a subselect,
                                //but we are not in the context of a subselect, 
                                //use the whole name as written (i.e., including the qualifier)
                                  (fld.isSubSelectField && !isSubSelect ?
                                        name.values.join('.') :
                                      //if a literal, always use the last value...
                                      //@todo: Using: select field as alias.alias from tbl1 should result to parse error
                                      //since there should be no "." in a given alias/name
                                        (name.values.length === 1 ? name.values[0] : name.values[1])
                                  ) :
                                  (name.value || name.toString());

                    }(fld, name, isSubSelect, NODES)).toString();
                                
            //determine the field key to use in the final fields of this project operation
            var _fKey     = fldNameDisplay
                 ,_fKeyUC = _fKey.toUpperCase();
                            
            //keep track of the field keys used. if used for the first time, label as 1;
            //otherwise, just increment it.
             _fieldKeys[_fKeyUC] = undefined === _fieldKeys[_fKeyUC] ? 1 : (function(){
             
                var count = _fieldKeys[_fKeyUC];
                
                _fKey      = _fKey + ":" + count;									
                _fKeyUC  = _fKey.toUpperCase();
                _fieldKeys[_fKeyUC] = 1;
                return (++count);
                
             }());
             
            _finalFields[_fKeyUC] = baseLib.apply(fld, {
                nameDisplay : fldNameDisplay
               ,fKey : _fKeyUC
               //this indicates if the user supplied an alias for the field reference
               ,useOrigName : field && name && (field.toString() === name.toString())
            });
            
            if( name ){
            
                var _n = name instanceof NODES.LiteralValue ? name.values.join('.').toString().toUpperCase() :
                              (name.value || name).toString().toUpperCase();

                if( field instanceof NODES.LiteralValue ){										
                    if( field.values.join('.').toUpperCase() !== _n && !_aliasMapping[_n] ){
                        _aliasMapping[_n] = _finalFields[_fKeyUC];
                    }
                }
                else{									
                    _aliasMapping[_n] = _finalFields[_fKeyUC];
                }
            
            }
        
        };

        var i, len, _userFld, fld, fldName, fldNameUC, literal, literalUC, fields, expr, _expr;
        var handleStar = function(idx, item, joinFields, literalUC){
                    
                //if there's a literal with the star, "item" contains the fieldkey; otherwise, idx contains it.
                var fKey = literalUC ? item : idx
                     ,fld = joinFields[fKey];

                if( fld.isAmbiguous ){
                
                    //if an alias was supplied, try using alias.fieldName
                    if( literalUC ){
                        fld = joinFields[[literalUC, fKey].join('.')];
                    }
                    
                    //just ignore ambiguous fields that are NOT qualified.
                    if( !fld.isQualified ){
                        return;
                    }
                    
                    //if qualified, force an error
                    this._checkIfValidField(fld, fld.nameDisplay);
                    
                }
                
                //do not include those fields that are mapped
                //and are not really needed because a mapping conflict did not occur (because mappingNew is still the original)
                if( fld.mappedField && fld.mappingNew === fld.mapping ){
                    return;
                }
                
                _regAsFinalField(fld, null, null, _finalFields, _fieldKeys, _aliasMapping, NODES);
                _numFields++;
                
        };
        
        var handleStarFields = function(literalUC, joinFields, joinMeta, literal){

            if( literalUC && (!joinMeta || !joinMeta[literalUC] || !joinMeta[literalUC].fIndex) ){
                throw new SQLError("could not prepare statement (1 no such table: " + literal + ")", _SQLErrorCodes.SYNTAX_ERR);
            }
            
            return  literalUC ? joinMeta[literalUC].fIndex : joinFields;
            
        };
        
        var procField = function(idx, item, allFields){
            handleStar.apply(this, [idx, item, joinFields, literalUC]);
        };
        
        for(i = 0, len = selectFields.length; i < len; i++){
                            
            _userFld = selectFields[i];
            fld = {};
            
            switch(true){
                
                //if a field reference
                case _userFld.field instanceof NODES.LiteralValue : {
                
                    this._checkIfValidExpr(_userFld.field, selectBlock);
                                        
                    fldName = _userFld.field.values.join('.');
                    //convert to uppercase to act as key
                    fldNameUC = fldName.toUpperCase();
                                            
                    //get the field's definition
                    fld = joinFields[fldNameUC];										

                    //define as a new field object (a separate instance)
                    fld = baseLib.apply({},
                         (fld.mappedField && fld.mapping === fld.mappingNew) ?
                         joinFields[fld.mappedField] :
                         fld
                    );
                                        
                    //now determine the names
                    _regAsFinalField(fld, _userFld.name, _userFld.field, _finalFields, _fieldKeys, _aliasMapping, NODES);
                    _numFields++;
                    break;
                    
                }
                
                //if a star/asterisk is used
                case _userFld instanceof NODES.Star : {
                
                    literal = _userFld.literal && _userFld.literal.value ? _userFld.literal.value.toString() : '';
                    literalUC = literal.toUpperCase();
                    fields = handleStarFields(literalUC, joinFields, joinMeta, literal);

                    baseLib.each(fields, procField, this);
                    
                    break;
                
                }
                
                //not a field reference, not a star (i.e., an expression)
                default : {

                    expr = _userFld.field;

                    this._checkIfValidExpr(expr, selectBlock);

                    _expr = {conditions : expr};
                    
                    this._preEvaluateExpression(_expr, selectBlock);
                    this._transformBooleanExpressions(_expr);
                    
                    _expr = {expr : _expr.conditions};

                    _regAsFinalField(_expr, _userFld.name, _expr, _finalFields, _fieldKeys, _aliasMapping, NODES);
                    
                    _numFields++;
                    
                }
                
            }
            
        }
       
        return {
            fields : _finalFields
           ,aliasMapping : _aliasMapping //keep track of the aliases because they might be used in GROUP BY or ORDER BY clause later.
           ,numFields : _numFields
        };
        
        
   }
 
   ,_getTableReference : function(tblSource){

        var NODES = sqlParser.nodes
             ,_result = {}
             ,aliasUC, fields, _f, meta;
        
        if( tblSource instanceof NODES.Table ){

            var origName      =  tblSource.name.values.join(".")
                 ,origNameUC  = origName.toUpperCase()
                 ,table            = this.storageEngine._getDBObj(origNameUC, 'table');

            if( !table ){
                throw new SQLError("could not prepare statement (1 no such table: " + origName + ")", _SQLErrorCodes.SYNTAX_ERR);
            }
            
            aliasUC	= !tblSource.win ? origNameUC : tblSource.win.value.toUpperCase();
            fields		= {};
            
            var schema = table.schema, columns = schema.columns, fieldName;
            for(fieldName in columns){

                _f = baseLib.apply({}, columns[fieldName]);
                
                fields[fieldName] = baseLib.apply(_f, {
                    aliasSourceUC : aliasUC
                   ,mappingNew : _f.mapping
                });
                
                //we create extra fields to handle scenario wherein the user will specify the source table
                //also tag those fields as "qualified fields"
                fields[aliasUC + "." + fieldName] = {
                    isQualified : true
                   ,mappedField : fieldName
                   ,mappingNew : _f.mapping
                   ,mapping : _f.mapping
                   ,aliasSourceUC : aliasUC
                   ,nameDisplay : _f.nameDisplay
                };
            }

            meta = {};
            meta[aliasUC] = {
                fIndex : schema.fIndex
               ,numOfRows : table.data.length
            };
            
            _result = {
                variable : origNameUC
               ,alias : aliasUC
               ,fields : fields
               ,meta : meta
            };
            
        }

        //we are in a sub-select
        else if ( tblSource instanceof NODES.SubSelect ){
            
            var alias		= !tblSource.name ? '' : tblSource.name.values.join(".");
            
            aliasUC	= alias.toUpperCase();
            
            //we are doing an "internal projection" here			
            var exp			= this._buildSelectTree(tblSource.select, true)
                ,fIndex		= []
                ,fDisplayNames = {};
                
            fields	= (function(){
            
                var _fields = {}, expFields = exp.fields, fieldName, _nmeDisp;
                var handleDisplayNames = function(fDisplayNames){
                    var count = fDisplayNames[_nmeDisp];
                    _nmeDisp = _nmeDisp + ":" + count;
                    fDisplayNames[_nmeDisp] = 1;
                    return (++count);
                };
                
                for(fieldName in expFields){
                    
                    _f = baseLib.apply({}, expFields[fieldName]);
                    
                    _nmeDisp = _f.nameDisplay;

                    fDisplayNames[_nmeDisp] = fDisplayNames[_nmeDisp] === undefined ? 1 : handleDisplayNames(fDisplayNames);
                    
                    delete _f.aliasSourceUC;

                    _fields[fieldName] = baseLib.apply(_f, {isSubSelectField : true, nameDisplay : _nmeDisp});
                    
                    //used in fieldsByAlias (see below)
                    fIndex = fIndex.concat([fieldName]);
                    
                    if( !aliasUC ){
                        continue;
                    }
                    
                    baseLib.apply(_fields[fieldName], {aliasSourceUC : aliasUC});
                    
                    _fields[aliasUC + "." + fieldName] = {
                         isQualified : true
                        ,mappedField : fieldName
                        ,mappingNew : _f.mapping
                        ,mapping : _f.mapping
                        ,aliasSourceUC : aliasUC
                        ,isSubSelectField : true
                        ,nameDisplay  : alias + '.' +  _nmeDisp
                    };

                }
                
                return _fields;
        
            }());
            
            _result = baseLib.apply(exp, {
                fields : fields				   
            });
            
            if( aliasUC ){

                meta = {};
                meta[aliasUC] = {fIndex : fIndex};
                
                baseLib.apply(_result, {
                    alias : aliasUC
                   ,meta : meta
                   ,isSubSelect : true
                });
                
            }				
            
        }
        
        else if( tblSource.isCorrelated === true ){
        
            _result = tblSource;
            
        }
        
        return _result;
    
    }
 
});

//expose the optimizer in webSQL package
baseLib.apply(baseLib.webSQL, {
    optimizer : QueryOptimizer
});

/**
@class: AbstractWebStorage


A very basic DBMS can be said to have two general areas or concerns: computation and storage.

Computational concerns pertains to the actual compution of data (i.e, Query Processing); these area takes care of implementing the various
relational operations (e.g. SELECT, JOIN, WHERE, etc.) and the implied constaints (e.g., PRIMARY KEY constraint).

Storage concerns pertains to how data is actually read from, and written to, permanent storage.

This class takes care of the computational concerns, and delegates the storage concerns to its subclasses (see below).

Note: Ideally, those two DBMS areas -- Storage and Computation -- should be implemented by separate classes; however,
at this point, combining the two doesn't result to confusion.
*/    
AbstractWebStorage = baseLib.extend({

    requestTransaction : function(sqlTrxnCallback){

        sqlTrxnCallback.apply(context, []);
    }
    
    ,aggOperators : {'MAX' : true, 'MIN' : true, 'COUNT' : true, 'SUM' : true, 'AVG' : true}
    
    ,supportedOperators : {
        
        '==' : function(v1, v2){
            return this.supportedOperators['='].apply(this, [v1, v2]);
        }
        
       ,'=' : function(v1, v2){
            
            //baseLib.isEqual is not used since it's more efficient to not invoke it.            
            
            //equality operations involving NULL should always evaluate to false
            if( v1 === null || v2 === null ){				
                return false;
            }
    
            //relation "equality" comparison treats 1 and "1" as equal. so convert everything to string.	
            v1 = v1 === undefined ? '' : v1.toString();
            v2 = v2 === undefined ? '' : v2.toString();
            
            return v1 === v2;
    
        }
    
       ,'>' : function(v1, v2){
            return v1 > v2;
       }
       
       ,'<' : function(v1, v2){
       
            return v1 < v2;
       }
       
       ,'<=' : function(v1, v2){
            return v1 <= v2;
       }
       
       ,'>=' : function(v1, v2){
       
            return v1 >= v2;
       }
       
       ,'<>' : function(v1, v2){
       
            return v1 !== v2;
       }
       
       ,'!=' : function(v1, v2){
        
            return v1 !== v2;
       }
       
       ,'+' : function(v1, v2){
            return v1 === null || v2 === null ? null : (v1 + v2);
       }
       
       ,'-' : function(v1, v2){
            return v1 === null  || v2 === null  ? null : (v1 - v2);
       }
       
       //concat operator
       ,'||' : function(v1, v2){
        
            if( v1 === null || v2 === null ){
                return null;
            }                        

            return String(v1) + String(v2);

       }
       
       ,'AND' : function(v1, v2){
            return v1 === true && v2 === true;
        }
        
       ,'OR' : function(v1, v2){
       
            return v1 === true || v2 === true;
        }

       ,'IN' : function(v, set){
        
            return set[v] !== undefined ? true : false;
       }
       
       ,'NOT_IN' : function(v, set){
       
            return !this.supportedOperators.IN(v, set);
       }
       
       ,'IFNULL' : function(v1, v2){
            return this._ifNull(v1, v2);
        }
        
       ,'CAST' : function(v, toType){
            return this._cast([{val : v, toType : toType}]);
        }
       
       ,'AVG' : function(args, tuple, fields, lastResult){
            
            var expr = args[0]
                 ,isDistinct = args[1] || false
                 ,val 			= parseInt(this._evalExpInRel(expr, tuple, fields, lastResult, true), 10)
                 ,lastRow	= lastResult ? lastResult.row : null
                 ,mapping	= lastResult ? lastResult.mapping : '';

                 
            var addToTuple = lastResult.addToTuple || 'AVG_META', f = {};
             if( addToTuple === mapping ){
                this._computeRuntimeFieldMapping(f, tuple, mapping);
                addToTuple = f.mappingNew;
             }
             
            var meta = lastRow[addToTuple] || {sum: 0, index : 0, values : {}}
                 ,runSum = parseInt(meta.sum, 10)
                 ,runIndx = meta.index
                 ,values = meta.values;
            
            if( isDistinct ){     
                if( values[val] ){
                    return lastRow[mapping];
                }
                values[val] = true;
            }
            runSum = runSum + val;
            runIndx = runIndx + 1;
            
            lastResult.row[addToTuple] = {sum : runSum, index : runIndx, values : values};
            lastResult.addToTuple = addToTuple;
            
            //rounds off to two decimal places.
            return Math.round((runSum / runIndx) * 100)/100;
            
       }
       
       ,'MAX' : function(args, tuple, fields, lastResult){

            var expr = args[0]
                 ,val 			= this._evalExpInRel(expr, tuple, fields, lastResult, true)
                 ,lastRow	= lastResult ? lastResult.row : null
                 ,mapping	= lastResult ? lastResult.mapping : ''
                 ,val_        = lastRow[mapping] === undefined ? val : lastRow[mapping];

            return val > val_ ? val : val_;
       }
       
       ,'MIN' : function(args, tuple, fields, lastResult){
       
            var expr        = args[0]
                 ,val 			= this._evalExpInRel(expr, tuple, fields, lastResult, true)
                 ,lastRow	= lastResult ? lastResult.row : null
                 ,mapping	= lastResult ? lastResult.mapping : ''
                 ,val_        = lastRow[mapping] === undefined ? val : lastRow[mapping];

            return val > val_ ? val_ : val;
            
       }
       
       ,'COUNT' : function(args, tuple, fields, lastResult, isEmptyOnAgg){
            
            if( isEmptyOnAgg ){
                return 0;
            }
            
            var field         = args[0]
                 ,isDistinct  = args[1] || false                 
                 ,lastRow	 = lastResult ? lastResult.row : null
                 ,mapping	 = lastResult ? lastResult.mapping : ''
                 ,val_         = lastRow[mapping] === undefined ? 0 : lastRow[mapping];

            if( isDistinct ){
            
                var addToTuple = lastResult.addToTuple || 'COUNT_DISTINCT_META', f = {};
                
                if( addToTuple === mapping ){
                   this._computeRuntimeFieldMapping(f, tuple, mapping);
                   addToTuple = f.mappingNew;
                }
                                
                var valCount = lastRow[addToTuple] || {}, key, handle = [], _handle = '', count = 0;
                //note: COUNT(DISTINCT *) is not really supported by WebSQL. just included here for completeness.
                if( field === '*' ){
                    for(key in tuple){
                        handle = handle.concat([tuple[key]]);
                    }
                    _handle = handle.join('-');
                }
                else{
                    _handle = this._evalExpInRel(field, tuple, fields, lastResult, true, isEmptyOnAgg);
                }                

                count = lastRow[mapping] ? (valCount[_handle] ? lastRow[mapping] : lastRow[mapping]+1) : 1;
                valCount[_handle] = 1;                
                
                lastResult.row[addToTuple] = valCount;
                
                lastResult.addToTuple = addToTuple;
                
                return count;
                
            }
           
            return val_ + 1;
            
       }
       
       ,'SUM' : function(args, tuple, fields, lastResult){
       
            var expr        = args[0]
                 ,val 			= this._evalExpInRel(expr, tuple, fields, lastResult, true)
                 ,lastRow	= lastResult ? lastResult.row : null
                 ,mapping	= lastResult ? lastResult.mapping : ''
                 ,val_        = lastRow[mapping] === undefined ? 0 : lastRow[mapping];
            
            return val_ + val;
            
       }
       
       ,'DATETIME' : function(dateStr){
        
            var formats = {
            
                //YYYY-MM-DD
                "1" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])!*$/g

                //YYYY-MM-DD HH:MM
                ,"2" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):([0-5][0-9])!*$/g
                
                //YYYY-MM-DD HH:MM:SS or YYYY-MM-DD HH:MM:SS.SSS
               ,"3" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])?(!*$|\.[000-999]{3}!*$)/g

               //YYYY-MM-DDTHH:MM
               ,"4" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])T([01][0-9]|2[0-3]):([0-5][0-9])!*$/g
               
               //YYYY-MM-DDTHH:MM:SS or YYYY-MM-DDTHH:MM:SS.SSS
               ,"5" : /^(\d{4})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])T([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])?(!*$|\.[000-999]{3}!*$)/g

               //HH:MM
               ,"6" : /^([01][0-9]|2[0-3]):([0-5][0-9])!*$/g
               
               //HH:MM:SS or HH:MM:SS.SSS
               ,"7" : /^([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])?(!*$|\.[000-999]{3}!*$)/g
               
               
               //NOW
               ,"NOW" : /^now!*$/gi
               
               //DDDDDDDDDD
               ,"TIMESTAMP" : /^\d{10}!*$/g

               
            };

            var getDate = function(seconds) {
   
               var date = seconds ? new Date(seconds*1000) : new Date();
   
               var yyyy = date.getFullYear().toString();

               var mm = (date.getMonth()+1).toString(); // getMonth() is zero-based
               mm = (mm[1]?mm:"0"+mm[0]);

               var dd  = date.getDate().toString();
               dd = dd[1]?dd:"0"+dd[0];
               
               var hh  = date.getHours().toString();
               hh = hh[1]?hh:"0"+hh[0];
                  
               var min = date.getMinutes().toString();
               min = min[1]?min:"0"+min[0];

               var sec = date.getSeconds().toString();
               sec = sec[1]?sec:"0"+sec[0];
               
               return yyyy + '-' + mm  + '-' + dd + " " + hh + ":" + min + ":" + sec;
              
            };
            
            var matched = '', key;
            for(key in formats){
            
                if( null !== formats[key].exec(dateStr) ){

                    //we have a match				
                    matched = key;
                    
                    if( !(matched === "NOW" || matched === "TIMESTAMP") ){
                    
                        if( dateStr.indexOf(".") !== -1 ){
                            dateStr = dateStr.split(".")[0];
                        }
                        
                    }
                    else{
                        
                        if( matched === 'NOW' ){
                            dateStr = getDate();
                        }
                        else{
                            dateStr = getDate(dateStr);
                        }
                        
                    }
                    
                    break;
                    
                }
                
            }

            return matched ? dateStr : null;
            
       }
       
	   ,'TIME' : function(str){
		
			var dateTime = this.supportedOperators.DATETIME.apply(this, [str])
			    ,parts = !dateTime ? [] : dateTime.split(' ');
				
			if(  parts.length === 0 ){
				return  null;
			}
			
			return parts.length === 1 ? '00:00:00' : parts[1];

	   }
	   
       ,'LIKE' : function(value, pattern){
            
            var colVal = value.toString(), regExp;
            
            colVal = colVal.toLowerCase();			
            colVal = colVal.replace(/(\r|\n)/g, ""); //replace all white characters (except blank string) in the column's value with empty string.
            
            pattern = pattern.toLowerCase();
			pattern = pattern.replace(/(\r|\n)/g, ""); //replace all white characters (except blank string) in the pattern with empty string.
			
            pattern = pattern.replace(/%/g, ".*");
            
            regExp = new RegExp('^' + pattern + '?$', "g"); 
			
            return regExp.test(colVal);

       }
       
       ,'IS' : function(v1, v2){
            
            //Note: websql returns 1 or 0 for IS operator, instead of the more intuitive TRUE or FALSE.            
            if( v1 === null || v2 === null ){
                return v1 === v2 ? 1 : 0;
            }
            
            return baseLib.isEqual(v1, v2, false, true) ? 1 : 0;
       }

       ,'IS NOT' : function(v1, v2){
       
            //Note: websql returns 1 or 0 for IS operator, instead of the more intuitive TRUE or FALSE.
            if( v1 === null || v2 === null ){
                return v1 !== v2 ? 1 : 0;
            }
            
            return !baseLib.isEqual(v1, v2, false, true) ? 1 : 0;
       }

       ,'CASE' : function(conditions, baseExpression, elseExpression, tuple, fields, allData, isInAggOp){
            
            var whenExp, thenExp
                ,caseFn = this.supportedOperators.CASE_CONDITION
                ,len = conditions.length
                ,i, condition, isPreEval;
            
            baseExpression = baseExpression !== undefined ? this._evalExpInRel(baseExpression, tuple, fields, allData, isInAggOp) : undefined;            
            
            for(i = 0; i < len; i++){
                
                condition = conditions[i];
                
                isPreEval = baseLib.isArray(condition);
                
                whenExp = isPreEval ? condition[0] : this._evalExpInRel(condition.arguments[0], tuple, fields, allData, isInAggOp);
                thenExp  = isPreEval ? condition[1] : undefined;                
                
                if(  caseFn.apply(this, [whenExp, thenExp, baseExpression, false]) === true ){
                    return thenExp !== undefined ? thenExp : this._evalExpInRel(condition.arguments[1], tuple, fields, allData, isInAggOp);                    
                }
                
            }
            
            return elseExpression === undefined ? null : this._evalExpInRel(elseExpression, tuple, fields, allData, isInAggOp);
            
       }
      
      ,'CASE_CONDITION' : function(whenExp, thenVal, baseExpr, isPreEval){
            
            isPreEval = isPreEval || false;
            
            if( isPreEval ){
                return [whenExp, thenVal];
            }
            
            //the !!whenExp intends to transform a value to its boolean literal equivalent.
            //so "", 0, null, undefined will be FALSE; the rest would be TRUE.
            return (baseExpr === undefined && !!whenExp) ||
                        (baseExpr !== undefined && this.supportedOperators['='].apply(this, [whenExp, baseExpr]));

       }
        
    }
    
    ,_proc : function(parseTree, result, cbSuccess, cbError, sqlTrxn){
    
        var __proc = function(){
        
            var pt = parseTree
                ,isUpdateOperator = pt.target !== undefined;
        
            try{
                
                var optimizer = new QueryOptimizer({storageEngine : this})
                    ,optimization = {newParseTree : {}, executionPlan : []};
                     
                optimizer.generatePlan(pt, optimization);
                
                if( isUpdateOperator ){
                    
                    var name = pt.target.value;
                    
                    //create or drop tbl
                    if(pt.fieldDef || pt.isDrop === true){
                        if( pt.isDrop === true ){						
                            this._dropTable(name, pt, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
                        }
                        else{
                            this._createTable(name, this._cleanCreateTableTree(pt), {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
                        }
                    }                    
                    //alter table (rename)
                    else if( pt.newName !== undefined ){
                        this._renameTable(name, pt, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
                    }
                    //insert
                    else if(pt.insertExpression){
                        this._insertToTable(name, optimization, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
                    }
                    //update
                    else if( pt.assignList ){
                        this._updateTableData(name, optimization, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
                    }
                    //delete
                    else{
                        this._deleteFromTable(name, optimization, {cbSuccess : cbSuccess, cbError : cbError}, sqlTrxn);
                    }
                    
                }
                //select statement
                else{

                    var _r = pt instanceof sqlParser.nodes.QueryPlan ?
                                 this._procExplainQueryPlan(optimization, optimizer) :
                                 this._procSelectExp(optimization.executionPlan, optimization.newParseTree);

                    baseLib.apply(_r, {rowsAffected : 0, insertId : undefined});
                    baseLib.apply(result, _r);

                    if( 'function' === typeof cbSuccess ){
                        cbSuccess.apply(context, [sqlTrxn, new SQLResultSet(result)]);
                    }
                    
                }
            
            }
            catch(e){
            
                //only call error callback if sql error. any other kind of errors (e.g. javascript native errors) will be rethrown.
                if (e instanceof SQLError && 'function' === typeof cbError) {
                    cbError.apply(context, [sqlTrxn, e]);
                    return;
                }
                
                throw e;                               
                
            }
            
        };
    
        this._prepare(__proc, this);
        
    }
    
    ,_prepare  : function(onReady, scope){
        onReady.apply(scope, []);		
    }

    ,_getApplicablePostIndexes : function(planIndexes, prevTables){
        
        var indexes = [], iPt, ptLen, alias, _post, i, len;
        
        for(iPt = 0, ptLen = prevTables.length; iPt < ptLen; iPt++){
        
            alias   = prevTables[iPt];
            _post = planIndexes[alias];
            
            if( _post ){
                for(i = 0, len = _post.length; i < len; i++){
                    indexes = indexes.concat([_post[i]]);
                }
            }
            
        }	

        return indexes;
        
    }
    
    ,_buildIndexedData : function(data, indexes, fields, indexedData, options){

        options = options || {};
        
        var rowIDOnly  = options.rowIDOnly || false
              ,i, len, j, jLen, row
              ,_row, keys, rootKey, root
              ,_key, _d, _tmp;

        for(i = 0, len = data.length; i < len; i++){
            
            row   = data[i];
            _row = rowIDOnly ? i : row;
            keys  = [];
                 
            for(j = 0, jLen = indexes.length; j < jLen; j++){
                keys = keys.concat([this._getFieldVal(row, indexes[j], fields)]);
            }
            
            rootKey = keys[0];
            _tmp     = indexedData[rootKey];
            
            indexedData[rootKey] =  !_tmp ? {d : [_row]} : (_tmp.d ? {d : _tmp.d.concat([_row])} : _tmp);
            
            root = indexedData[rootKey];
            
            for(j = 1, jLen = keys.length; j < jLen; j++){
            
                _key = keys[j];
                
                if( !root[_key] ){
                    delete root.d;            
                    root[_key] = {d : [_row]};					
                }
                else{
                    _d = root[_key].d;
                    if( _d ){
                        delete root[_key].d;
                        root[_key].d = _d.concat([_row]);
                    }
                }
                
                root  = root[_key];
                
            }		
            
        }
        
    }

    /*
        data -- an array of objects/rows
        indexes -- an array of field names, which will be indexed. the order of indexing is according to the order of the field names in the array
        fields - a key-value object of field kay and field object. this will serve in retrieving information about a given field.
        
        //@todo : building of keys should consider datatype
        return value example:
            {
                idxVal1 : {
                    idxVal2 : {
                        d : [...data...]
                    }
                }
            }
    */	
    ,_getIndexedData : function(data, indexes, fields, options){
    
        var _indexedData = {};
        
        this._buildIndexedData(data, indexes, fields, _indexedData, options);
        
        return _indexedData;
        
    }
    
    /*
        haystack -- an object of the same structure as returned by _buildIndexedData; i.e., it is an indexed data.
        needle -- an row/object that needs to be found on haystack
        conds  -- an array of conditions; each item in the condition must be satisfied so that this function would not return an empty array.
        fields -- a set of fields (same as in _buildIndexedData); helps in retrieving a value in the needle.
        fieldType -- possible values are only 'field' and 'refField'.
                        remember that both are properties in a cond object in conds.						
                        the purpose is to determine a field name in the needle based on the value of field or refField,
                        and hence identify a value in the needle.
                        that value will then be used to search through the haystack(index).						
        @todo: comparison of keys should consider data type
    */
    ,_searchIndexedData : function(haystack, needle, conds, fields, fieldType){
    
        /*
            function to recursively merge the matches
            obj1 is the existing set of matches
            obj2 is the new matches.
        */
        var recApply = function(obj1, obj2){
        
            var key, _m, m;
            for(key in obj2){			
                _m = obj1[key];
                m = obj2[key];				
                if( _m === undefined ){
                    obj1[key] = m;
                }
                else if( _m.d && m.d ){
                    _m.d = _m.d.concat(m.d);
                }
                else{
                    recApply(_m, m);
                }
            }
            
        };
        
        var i, len, cond, termOp, operator, mainField, v
            ,isReference, otherFldType, otherField, _tempData
            ,idxVal, tuple, mappingToUse
            ,matches, _data;
            
        for(i = 0, len = conds.length; i < len; i++){
        
            cond        = conds[i];
            termOp    = cond.term; //an instance of sqlParser.nodes.Op
            operator  = cond.op.toUpperCase();
            mainField = cond[fieldType];
                
            switch(operator){
            
                //equality operator. directly access the index.
                case 'IS':
                case '=':
                case '==': {
                    v = this._getFieldVal(needle, mainField, fields);
                    //use only direct acess if v is not null, or operator is IS.
                    if( v !== null || operator === 'IS' ){
                        haystack = haystack[v];
                    }
                    break;
                }
                
                default : {

                    //if true, this search does not involve a constant -- only field references.
                    isReference = (cond.refField !== undefined);
                    otherFldType = isReference ? (fieldType === 'refField' ? 'field' : 'refField') : '';
                    otherField    = isReference ? cond[otherFldType] : '';
                    _tempData = {};

                    for(idxVal in haystack){
                        
                        idxVal = idxVal === 'null' ? null : idxVal;
                        
                        
                        /*
                            if isReference, we will use needle as the tuple,
                            since conceptually the operation is similar to comparing to fields in a row.
                            if not isReference, tuple will just contain one field;
                            and the value of that sole field will be evaluated with a constant.
                        */
                        tuple = isReference ? needle : {};
                        mappingToUse = fields[isReference ? otherField : mainField].mappingNew;

                        tuple[mappingToUse] = idxVal;
                        
                        if( !this._evalExpInRel(termOp, tuple, fields) ){
                            continue;
                        }
                        
                        //if true
                        matches	= haystack[idxVal];
                        _data		= matches ? matches.d : null;

                        if( matches ){
                            if( _data ){
                                baseLib.apply(_tempData, {
                                    d : !_tempData.d ? _data : _data.concat(_tempData.d)
                                });
                            }
                            else{								
                                recApply.apply(this, [_tempData, matches]);								
                            }
                        }

                    }
                    
                    haystack = _tempData;

                }
                
            }

            if( !haystack ){
                return [];
            }
            
        }
        
        return haystack.d || [];
        
    }
    
    ,_searchData : function(haystack, conds, plan, options){
    
        options = options || {};
        
        var rowIDOnly	= options.rowIDOnly || false
             ,jLen			= conds.length
             ,result			= []
             ,fields			= plan.fields
             ,i, len, tuple, isOk, j
             ,_c;

        for(i = 0, len = haystack.length; i < len; i++){

            tuple = haystack[i];
            isOk = true;

            for(j = 0; j < jLen; j++){
                
                _c = conds[j];
                isOk = this._evalExpInRel(_c.term, tuple, fields);
                if( !isOk ){
                    break;
                }
                
            }
            
            if( !isOk ){
                continue;
            }
            
            result = result.concat([rowIDOnly ? i : tuple]);

        }
        
        return result;
        
    }
    
    ,_setupPlanData : function(plan, prevTables, result, options){
    
        var rows	= plan.data || (this.readFromStorage(plan.variable) || [])
             ,_pre	= plan.pre;

        var sortIndexFields = function(col1, col2, fields){
        
            var f1		= fields[col1.field]
                 ,f2		= fields[col2.field];
                 
            f1		= f1.mappedField ? fields[f1.mappedField] : f1;
            f2		=	f2.mappedField ? fields[f2.mappedField] : f2;
            
            return (f1.numOfDistinctValues || 0)  > (f2.numOfDistinctValues || 0);
        };
        
        if( _pre ){
            rows = this._searchData(rows, _pre, plan, options);
        }
        
        /*
        if a post index was detected, then pre-index the data returned by this function.
        */		
        var postIndexes = !plan.post ? [] : this._getApplicablePostIndexes(plan.post, prevTables)
             ,postIndexesLen = postIndexes.length
             ,hasPostIndexes = postIndexesLen > 0
             ,postIndexNames = [];

        if( hasPostIndexes ) {

            postIndexes.sort(function(f1, f2){
                return sortIndexFields(f1, f2, plan.fields);
            });
            
            var i, len, _idx;
            for(i = 0, len = postIndexesLen; i < len; i++){
                _idx = postIndexes[i];
                postIndexNames = postIndexNames.concat([_idx.field]);
            }

        }
        
        baseLib.apply(result, {
            rows : hasPostIndexes ? this._getIndexedData(rows, postIndexNames, plan.fields) : rows
           ,indexes : postIndexes
        });
        
    }
        
    ,_joinRowNull : function(row1, row2Fields){
    
        var row = row1, i, len, f;
        for(i = 0, len = row2Fields.length; i < len; i++){
            f = row2Fields[i];						
            row[f.mappingNew] = null;
        }
        
        return row;
    }
    
    ,_joinRow : function(row1, row2, row2Fields){
    
        var row = row1, i, len, f, v;
        for(i = 0, len = row2Fields.length; i < len; i++){
        
            f = row2Fields[i];
            v = row2[f.mapping];

            row[f.mappingNew] = v === undefined ? row2[f.mappingNew] : v;
            
        }
        
        return row;
        
    }
    
    ,_buildFinalRowForm : function(row, finalFields){
    
        var _row = {}, i, len, f, fName;

        for(i = 0, len = finalFields.length; i < len; i++){
            
            f = finalFields[i];
            fName = f.useOrigName ? f.nameOrig : f.nameDisplay;
            
            if( undefined === _row[fName] ) {
            
                Object.defineProperty(_row, fName, {
                    configurable : false
                   ,enumerable : true
                   ,writable : false
                   ,value : this._parseValue(row[f.mappingNew], f.type)
                });

            }
            
        }
        
        return _row;
    
    }
    
    //return the value of a given field found in a given row (with the possible help of the set of fields)
    ,_getFieldVal : function(row, fieldName, fields){
    
        fieldName  = fieldName.toUpperCase();
        
        var fieldVal = row[fieldName];
        
        //try first the given fieldName; if it exists in row, then return it immediately.
        if( undefined !== fieldVal ){
            return fieldVal;
        }
        
        var field = fields[fieldName]
                         //if the field is a mapped field but its new mapping is still equal
                         //to its original mapping, then use the field it is mapped to.
             ,f      = field ? ((field.mappedField && field.mappingNew === field.mapping) ? fields[field.mappedField] : field) : null;
        
        if( !f ){
            return undefined;
        }
        
        fieldVal = row[f.mapping];
        return fieldVal !== undefined ? fieldVal : row[f.mappingNew];
        
    }	

    //process select statements
    ,_procSelectExp : function(executionPlan, parseTree){
    
        var unions = parseTree.unions
             ,finalFields
             ,allData = [];
        
        if( unions ){

            var uIdx, fieldCount, ep, u,
                _aFinalFields, _origFinalFields, origFieldsLen, origFieldsIdx,
                uLen = unions.length, isUnionAll = false, origFinalFields = [], _result = {};

            for(uIdx = 0; uIdx < uLen; uIdx++){

                ep = executionPlan[uIdx];
                u = unions[uIdx];
                isUnionAll = u.isUnionAll || false;
                if( uIdx === 0 ){
                    origFinalFields  = u.select.aFinalFields;
                }
                else{
                
                    _aFinalFields = u.select.aFinalFields;
                    
                    for(origFieldsIdx = 0, origFieldsLen = origFinalFields.length; origFieldsIdx < origFieldsLen; origFieldsIdx++){
                    
                        _origFinalFields = origFinalFields[origFieldsIdx];
                        baseLib.apply(_aFinalFields[origFieldsIdx], {
                            fKey : _origFinalFields.fKey
                           ,nameDisplay : _origFinalFields.nameDisplay
                           ,useOrigName : false
                        });
                        
                    }
                    
                    u.select.aFinalFields = _aFinalFields;
                    
                }
                
                _result = this._procSelectExp(ep, u);
                
                if( uIdx === 0 ){
                    finalFields = _result.fields;
                    fieldCount = _result.fieldCount;
                }

                allData = allData.concat(_result.data);

                if( isUnionAll === false && uIdx > 0){ //shall we process further the result of the UNION operator ?
                    
                    _aFinalFields = u.select.aFinalFields;                    
                    
                    for(origFieldsIdx = 0, origFieldsLen = origFinalFields.length; origFieldsIdx < origFieldsLen; origFieldsIdx++){
                        _aFinalFields[origFieldsIdx].mapping = origFinalFields[origFieldsIdx].nameDisplay;
                        _aFinalFields[origFieldsIdx].mappingNew = origFinalFields[origFieldsIdx].nameDisplay;
                    }

					//manipulate properties of the first execution plan and the parsetree so it is treated as a new and separate query.
                    baseLib.apply(ep[0], {
                        data : allData
                       ,jFields : _aFinalFields
					   ,pre : null
                    });
                    
					baseLib.apply(u.select, {
						expFields : []
					   ,aggExpFields : []
					   ,aFinalFields : _aFinalFields
					   ,distinct : true //important. this will eliminate the duplicates, which is required by the definition of the UNION ALL operator.
					});

					baseLib.apply(u, {
						where : {
							otherCond : []
						}
					});
					
					//use only the first execution plan. this is a random choice.
                    allData = this._procSelectExp([ep[0]], u).data;
                    
                }
               
            }
            
            return {
                data : allData
               ,fields : finalFields
               ,fieldCount : fieldCount
            };
            
        }
        
        var joinPlan			= executionPlan
             ,jpCnt				= joinPlan.length
             ,joinFields		= parseTree.fields
             ,select				= parseTree.select
             ,aFinalFields     = select.aFinalFields
             ,where				= parseTree.where
             ,otherCond  	= where.otherCond
             ,otherCondLen = otherCond.length
             ,group				= parseTree.group
             ,order				= parseTree.order
             ,limit				= parseTree.limit
             ,distinct			= select.distinct
             ,isEarlyBuild		= !group && !order && !distinct && !select.hasAggOperator
             ,prevTables		= []
             ,i, len, plan;
        
        finalFields    	= select.finalFields;
        
        /*
        no reason to do all the trouble of computing other expressions if
        the select expression is guaranteed to be an empty relation/table.
        if so, bail-out early.
        */
        if( where.conditions === false ){
            return {
                data : 	[]
                ,fields : finalFields
                ,fieldCount : aFinalFields.length
            };
        }
        
        /*
            if where condition is a TRUE literal, usually otherCond has 1 element in it, and that element is a boolean TRUE.
            if so, empty out otherCond so it won't be needlessly evaluated.
        */
        if( otherCond.length === 1 && otherCond[0] === true ){		
            otherCond = [];
            otherCondLen = 0;
        }
        
        
        //if the select involves more than 1 tables...
        if( jpCnt > 1 ){
            //"open" the tables; or build the necessary indices...
            var planData;
            for( i = 0; i < jpCnt; i++){

                planData = {};
                plan   = joinPlan[i];
                
                /*
                only setup if plan has no rows and has no indexes 
                (usually, a plan with an existing rows and indexes is a subquery)
                */
                if( !(plan.rows || plan.indexes) ){
                    this._setupPlanData(plan, prevTables, planData);
                }
                
                baseLib.apply(plan, planData);
                
                prevTables = prevTables.concat([plan.alias]);
                
            }
        }
        
        /********the main JOIN operation********/
        
        var jp1			= joinPlan[0]
            ,jp1JFields = jp1.jFields
            ,result		= {allData : [], isEarlyBuild : isEarlyBuild, hasAggOperator : select.hasAggOperator};

        //if there is only 1 table involved in the select operation.
        if( jpCnt === 1 ){
            //if there are pre conditions, put those in the otherConditions.
            //in short, pre-conditions are not anymore pre-evaluated since there is only 1 table involved.
            //this avoids scanning the table twice.
            if( jp1.pre ){
                var _pre = jp1.pre;
                for(i = 0, len = _pre.length; i < len ; i++){
                    otherCond = [_pre[i].term].concat(otherCond);
                }
                otherCondLen = otherCond.length;
            }
            
            jp1.rows =  jp1.data || (this.readFromStorage(jp1.variable) || []);
        }
        
        var jp1Rows = jp1.rows, nT1, t1RowLen, _row, isOk, _cond
            ,idxCond, nNextTables, _finds = [], needles = [], ndlIdx, ndlLen
            ,needle, matches = [], mLen, m, match;
        
        if( jpCnt === 1 ){
            for(nT1 = 0, t1RowLen = jp1Rows.length; nT1 < t1RowLen; nT1++){
            
                _row	= this._joinRow({}, jp1Rows[nT1], jp1JFields);
                isOk	= true;
                     
                for(idxCond = 0; idxCond < otherCondLen; idxCond++){
                
                    _cond = otherCond[idxCond];
                    isOk = this._evalExpInRel(_cond, _row, joinFields);
                    if( !isOk ){
                        break;
                    }
                    
                }
                
                if( !isOk ){
                    continue;
                }
                
                this._procFinalFields(_row, finalFields, aFinalFields, joinFields, group, select, result);
            }
        }
        else{
        
            for(nT1 = 0, t1RowLen = jp1Rows.length; nT1 < t1RowLen; nT1++){

                _finds = [this._joinRow({}, jp1Rows[nT1], jp1JFields)];
                
                for(nNextTables = 1; nNextTables < jpCnt; nNextTables++){

                    plan		= joinPlan[nNextTables];
                    needles	= [].concat(_finds);//the items to find
                    
                    _finds  = [];

                    for(ndlIdx = 0, ndlLen = needles.length; ndlIdx < ndlLen; ndlIdx++){
                    
                        needle			= needles[ndlIdx];
                        matches		= !plan.indexes.length ? plan.rows : this._searchIndexedData(plan.rows, needle, plan.indexes, joinFields, 'refField');
                        mLen			= matches.length;
                        
                        if( matches.length === 0 && plan.operator === 'OUTERJOIN'){
                            matches = [this._joinRowNull(needle, plan.jFields)];
                            mLen = 1;
                        }
                        
                        for(m = 0; m < mLen; m++){
                            match = matches[m];
                            _finds = _finds.concat([this._joinRow(baseLib.apply({}, needle), match, plan.jFields)]);
                        }

                    }
                    
                }

                for(i = 0, len = _finds.length; i < len; i++){
                    
                    _row = _finds[i];
                    isOk = true;
                    
                    for(idxCond = 0; idxCond < otherCondLen; idxCond++){
                        _cond = otherCond[idxCond];
                        isOk = this._evalExpInRel(_cond, _row, joinFields);
                        if( !isOk ){
                            break;
                        }
                    }
                    
                    if( !isOk ){
                        continue;
                    }

                    this._procFinalFields(_row, finalFields, aFinalFields, joinFields, group, select, result);
                    
                }

            }
    
        }
        
        /********end of JOIN operation********/
        
        allData = result.allData;
        
        if( !result.isEarlyBuild ){
        
            //a select that used an aggregate operator but did not specify a group clause.
            //example: SELECT sum(field1) from tbl1
            //This always returns a single row. Thus, DISTINCT and ORDER BY operations are needless.
            if( !group && result.hasAggOperator ){
            
                //if ending data is empty, create a dummy row, and perform a computation.
                if( allData.length === 0 ){
                
                    //create a dummy row whose fields are all null.
                    _row = this._joinRowNull({}, (function(joinFields){
                        var key, fields = [];
                        for(key in joinFields){
                            fields = fields.concat([joinFields[key]]);
                        }
                        return fields;
                    }(joinFields)));          

                    this._procFinalFields(_row, finalFields, aFinalFields, joinFields, group, select, result, true);
                    
                    allData = result.allData;
                    
                }
                
                allData = [this._buildFinalRowForm(allData[0], aFinalFields)];
                
            }
            else{
            
                if( group ){
                    allData = this._unpackGroupedData(allData, [], aFinalFields, !distinct && !order);
                }
                
                if( distinct ){
                    var _sort = {mapping : select.distinctMapping, collation : '', direction : 'ASC'};
                    allData = this._performSort(allData, [_sort], aFinalFields, !order, true);						
                }
                
                if( order ){
                    allData = this._performSort(allData, order, aFinalFields, true, false);
                }
            }

        }
        
        if( limit ){
        
            var start = limit.pageNo ? this._evalExpInRel(limit.pageNo, {}, {}, false, false) : 0
                 ,end  = this._evalExpInRel(limit.numRows, {}, {}, false, false);
            
            allData = allData.splice(start, end);
        
        }
        
        return {
            data : allData
           ,fields : finalFields
           ,fieldCount : aFinalFields.length
        };

    }

    ,_procFinalFields : function(tuple, finalFields, aFinalFields, joinFields, group, select, result, isEmptyOnAgg){
            
        var isDistinct          = select.distinct
             ,allFields            = select.allFields
             ,lastGrouped     = {}
             ,distinctHandle   = []
             ,aggExpFields    = []
             ,expFields         = []
             ,grp
             ,field, expr, v, mappingNew, lastResult, addToTuple, isGroupingExpr
             ,i, len, distinctMapping, distinctField ,groupIndexes, groupings;
        
        //ORDER BY clause expressions will not be processed here since some tuples might eliminated if there's
        //a DISTINCT clause.

         //evaluate here all expressions in SELECT that does not involve aggregate operations
         //and also all expressions in GROUP BY
         expFields = select.expFields;
         for(i = 0, len = expFields.length; i < len; i++){
         
            field	= expFields[i];
            expr	= field.expr;
            
            mappingNew = field.mappingNew;
            if( !mappingNew ){
                this._computeRuntimeFieldMapping(field, tuple);
                mappingNew = field.mappingNew;
            }
            
            isGroupingExpr = field.isGroupingExpr;
            
            //only use allFields (the combination of aliases and joined fields) when we are evaluating a group expression.
            v = this._evalExpInRel(expr, tuple, isGroupingExpr ? allFields : joinFields, {}, false);
            
            tuple[mappingNew] = v;
            
            //update the field mapping for GROUP BY items that are expressions.
            if( isGroupingExpr && group ){
                
                if( !groupings ){
                    groupings = group.groupings || [];
                }
                
                groupings[field.groupIndex] = baseLib.apply(groupings[field.groupIndex], {field : {mappingNew : mappingNew}});

            }
             
         }
         
         if( group && groupings ){
            group.groupings = groupings;
         }
         
        // if there is grouping, get the last record for a given grouping.
        // to do that, group.idxData is traversed using the current tuple/record and the traversal path used is group.indexes.
        // if there is no grouping for the current tuple/record
        // (i.e., because this is the first call to _procFinalFields; or the current record is not yet found in group.idxData)
        // the variable lastGrouped is set to NULL.
        if( group && group.idxData && group.indexes ){
        
            var grpIndexes = group.indexes                
                 ,_data		   = group.idxData
                 ,grpIdx, _d;
                 
            for(i = 0, len = grpIndexes.length; i < len; i++){
                grpIdx = grpIndexes[i];
                _data = _data[this._getFieldVal(tuple, grpIdx, joinFields)];

                if( !_data ) {
                    break;
                }

                _d = _data.d;
                if( _d ){
                    lastGrouped = _d[_d.length-1];
                    break;
                }            
            }
            
        }

        // A SELECT operation that has an aggregate operation in it's select clause 
        // and with no GROUP BY clause always returns just 1 row.
        // also, the last grouped data will be that sole row.        
        else if ( result.hasAggOperator ){
            lastGrouped = result.allData[0] || {};
        }
        
        //evaluate here all expressions in SELECT that involved aggregate operators.
        aggExpFields = select.aggExpFields;

        for(i = 0, len = aggExpFields.length; i < len; i++){
                        
            field	= aggExpFields[i];
            expr	= field.expr;
            
            mappingNew = field.mappingNew;
            if( !mappingNew ){
                this._computeRuntimeFieldMapping(field, tuple);
                mappingNew = field.mappingNew;
            }
            
            lastResult = {row : lastGrouped, mapping : mappingNew, addToTuple : field.addToTuple};
            
            v = this._evalExpInRel(expr, tuple, joinFields, lastResult, false, isEmptyOnAgg);
            
            addToTuple = lastResult.addToTuple;
            if( addToTuple !== undefined ){
                field.addToTuple = addToTuple;
                tuple[addToTuple] = lastResult.row[addToTuple];                
            }
            
            tuple[mappingNew] = v;
            
        }

        if( result.isEarlyBuild ){
            result.allData = result.allData.concat([this._buildFinalRowForm(tuple, select.aFinalFields)]);
            return;				
        }
        
        if( isDistinct ){
        
            for(i = 0, len = aFinalFields.length; i < len; i++){
                field	= aFinalFields[i];
                distinctHandle = distinctHandle.concat([tuple[field.mappingNew]]);
            }
            
            distinctMapping = select.distinctMapping;
            
            if( !distinctMapping ){
                distinctField = {nameDisplay : 'DISTINCT'};
                this._computeRuntimeFieldMapping(distinctField, tuple);
                distinctMapping = distinctField.mappingNew;
                select.distinctMapping = distinctMapping;
            }            
            
            tuple[distinctMapping] = distinctHandle.join('-');
            
        }

        //has a group by clause specified...
        if( group ){
        
            groupIndexes  = group.indexes || [];
            
            if( groupIndexes.length === 0 ){
            
                groupings   = group.groupings;
                
                //take note of the group indexes; do not process anymore the grouping expressions
                //since they are already processed above.
                for(i = 0, len = groupings.length; i < len; i++){                    
                    grp = groupings[i];
                    field		 	= grp.field;
                    groupIndexes = groupIndexes.concat([field.mappingNew || field.mapping]);
                }
                
                group.indexes = groupIndexes;
            }            
            
            if( !group.idxData ) {
                group.idxData = {};
            }
            
            this._buildIndexedData([tuple], groupIndexes, {}, group.idxData);
            
            result.allData = group.idxData;
            
            return;
            
        }
        
        //no group by clause specified, but at least one aggregate operator was encountered...
        if( result.hasAggOperator ){
            
            baseLib.apply(result, {
                allData : [tuple]               
               ,isEarlyBuild : false
            });
            
            return;
            
        }

        result.allData = result.allData.concat([tuple]);
        
    }
    
    ,_computeRuntimeFieldMapping : function(field, tuple, except){
    
        except = except || '';
        var _count = 0, mapping, _m;
        mapping = field.mappingNew;
        if( !mapping ){
        
             _m = mapping	= field.nameDisplay ? field.nameDisplay.toUpperCase() : "EXPR";
             
             while( _m === except || tuple[_m] !== undefined ){
                _m = mapping  + ":" + (++_count);
            }
            _m = _m.toUpperCase();
            mapping = _m;
            field.mapping = mapping;
            field.mappingNew = mapping;
            
        }
        
    }
    
    ,_unpackGroupedData : function(groupIdxData, data, finalFields, earlyBuild){
        
        //add this check to avoid too much recursion.
        if( baseLib.isArray(groupIdxData) ){
        
            return groupIdxData;
        }
        
        data = data || [];
        var key, _tmp, _d;
        for(key in groupIdxData){
        
            _tmp = groupIdxData[key];			
            _d     = _tmp.d;
            
            data = !_d ? this._unpackGroupedData(_tmp, data, finalFields, earlyBuild) : 
                        data.concat([
                            earlyBuild ?
                            this._buildFinalRowForm(_d[_d.length-1], finalFields) :
                            _d[_d.length-1]
                        ]);
        }
        
        return data;
    
    }
    
    ,_performSort : function(data, sortFields, finalFields, isFinalForm, elimDuplicates){
    
        data.sort(baseLib.sortFn(sortFields));

        var _d = [], lastRow = '', i, len, row, currRow;
        for(i = 0, len = data.length; i < len; i++){
            
            row = data[i];
            if( elimDuplicates ){
            
                currRow = row[sortFields[0].mapping];
                if( i > 0 && currRow === lastRow ){
                    continue;
                }
                
                lastRow = currRow;
            
            }
            
            _d = _d.concat(isFinalForm ? this._buildFinalRowForm(row, finalFields) : row);
            
        }
        
        return _d;
        
    }	
    
    ,_cleanCreateTableTree : function(pt){
        
        //todo: Implement checking of only 1 PK in table. with message
        //could not prepare statement (1 table "c" has more than one primary key)
        
        var _pt = {fields : {}, fIndex : [], fIndexOrigNames : {}, ifNotExists : pt.ifNotExists}
            ,pkField = ''
            ,autoIncField = ''
            ,nonNullFields = {}
            ,idx, len, f, name;

        for(idx = 0, len = pt.fieldDef.length; idx < len; idx++){
        
            f       = pt.fieldDef[idx];
            name = (f.field.value).toUpperCase();
            
            if( f.isPK ){
                if( pkField !== '' ) {//PK is already defined
                    throw new SQLError('could not prepare statement (1 table "' + pt.target.value + '" has more than one primary key)', _SQLErrorCodes.SYNTAX_ERR);
                }
                pkField = name;
            }
            
            if( f.isAutoInc ) {
            
                if( !f.isPK || f.type.value.toString().toUpperCase() !== 'INTEGER' ){
                    throw new SQLError("could not prepare statement (1 AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY)", _SQLErrorCodes.SYNTAX_ERR);
                }                
                if( autoIncField !== '' ){//auto inc field already defined
                    throw new SQLError('could not prepare statement (1 near "autoincrement": syntax error)', _SQLErrorCodes.SYNTAX_ERR);
                }
                autoIncField = name;
                
            }
            
            _pt.fields[name] = {
                type : f.type.value
               ,name : name
               ,nameOrig : f.field.value
               ,nameDisplay : f.field.value
               ,alias : f.field.value
               ,aliasOrig : name
               ,ordPosition : idx
               ,isAmbiguous : false
               ,isAutoInc  : f.isAutoInc
               ,isNullable : f.isNullable
               ,isPK : f.isPK
               ,mapping : name
               
               ,numOfDistinctValues : 0
               ,minVal : 0
               ,maxVal : 0
               ,distinctValues : {}
            };
            
            _pt.fIndex[idx] = name;
            _pt.fIndexOrigNames[name] = f.field.value;
            
            if(  f.isNullable === false ){
                nonNullFields[name] = true;
            }
            
        }       
        
        _pt = baseLib.apply(_pt, {
            pkField : pkField
           ,keys : {}
           ,autoIncField : autoIncField
           ,nonNullFields : nonNullFields
        });

        
        return _pt;

   }

    ,_procExplainQueryPlan : function(optimization){
    
        var listPlans = function(plans){
            
            var _listPlans	= []
                 ,prevs		= [] //previous tables
                 ,i, len, plan, row;

            var desc = function(plan, prevs){
                   
                var pre = plan.pre || []
                     ,post = plan.post || {}
                     ,indexes = []
                     ,i, len, _p;
                     
                for(i = 0, len = pre.length; i < len; i++){
                    _p = pre[i];
                    indexes = indexes.concat([_p.field + ' ' + _p.op + ' ' + '?']);
                }

                var key, _post, j, jLen;
                for(i = 0, len = prevs.length; i < len; i++){
                
                    key    = prevs[i];
                    _post = post[key];
                    
                    if( !_post ){
                        continue;
                    }
                    
                    for(j = 0, jLen = _post.length; j < jLen; j++){
                        _p = _post[j];
                        indexes = indexes.concat([_p.field + ' ' + _p.op + ' ' + '?']);
                    }
                    
                }
                
                var idxCnt = indexes.length;
                return (idxCnt > 0 ? indexes.join(" and ") : "")  + "(~" + (plan.estRows/Math.pow(10, idxCnt)).toString() + ")";
                
           };
           
           for(i = 0, len = plans.length; i < len; i++){
            
                plan = plans[i];
                //if a union plan
                if( plan instanceof Array ){
                
                    _listPlans = _listPlans.concat(listPlans.apply(this, [plan]));
                    if( i < len - 1){
                        _listPlans = _listPlans.concat([{
                            order  : 0
                           ,from   : 0
                           ,table : ''
                           ,path : ''
                           ,desc : "EXECUTE UNION"
                        }]);
                   }
                    
                   continue;
                   
                }
                
                if( plan.isCorrelated ) {
                    prevs	= prevs.concat([plan.alias]);
                    continue;
                }
                
                row = {
                    order  : i
                   ,from   : plan.from
                   ,table : plan.alias
                   ,path : plan.physicalOp
                   ,desc :  desc(plan, prevs)
                };
            
                prevs		= prevs.concat([plan.alias]);
                _listPlans	= _listPlans.concat(row);

            }
            
            return _listPlans;
            
        };
        
        var plansOutput	= []
            ,executionPlan	= optimization.executionPlan || []
            ,subQueries		= optimization.executionPlanSQ || [];
        
        plansOutput = listPlans.apply(this, [executionPlan]);
        
        if( subQueries.length > 0 ){
        
            var i, len, sq;
            for(i = 0, len = subQueries.length; i < len; i++){
            
                sq = subQueries[i];
                
                //just a signifier that a subquery is involved.
                plansOutput = plansOutput.concat([{
                    order  : 0
                   ,from   : 0
                   ,table : ''
                   ,path : ''
                   ,desc : "EXECUTE CORRELATED SUBQUERY"				   
                }]);
            
                plansOutput = plansOutput.concat(listPlans.apply(this, [sq.ep]));
            
            }
            
        }
        
        return {
            data : plansOutput
           ,fields : {}
        };	
        
    }
    
    ,_procSubQuery : function(executionPlan, parseTree, isInOperator){
    
        var result = this._procSelectExp(executionPlan, parseTree)
             ,data  = result.data;
        
        /*
           a subquery in sqlite must only have 1 field.
           however, they are allowed to have more than 1 rows even in field expressions.
           if in field expression, the value return is the value of the sole field of what happens to be the first row.
        */
        if( result.fieldCount > 1 ){
            
            throw new SQLError("could not prepare statement (1 only a single result allowed for a SELECT that is part of an expression)", _SQLErrorCodes.SYNTAX_ERR);
        }		
        
        if( isInOperator ){
        
            var val = {}, i, len, row, key;
            for(i = 0, len = data.length; i < len; i++){
                row = data[i];
                for(key in row){
                    val[row[key]] = true;
                }
            }
            
            return val;
            
        }
        
        var v = null;
        
        if( data.length < 1 ){
            return v;
        }
        
        baseLib.each(data[0], function(_idx, _val, _data){
                v = _val;
                return false;
        }, this);
        
        return v;
        
    }
    
    //evaluate expressions in the context of relational expressions (i.e., mostly used in join and where expressions)
    ,_evalExpInRel : function(exp, tuple, fields, allData, isInAggOp, isEmptyOnAgg){
    
        //this variable indicates if we are in an empty result set but has an aggregate method on the SELECT clause.
        //primarily used by COUNT.
        isEmptyOnAgg = isEmptyOnAgg || false;
        
        allData      = allData || [];
        isInAggOp = isInAggOp || false; //whether we are in the middle of an aggregate operator
        
        var NODES		= sqlParser.nodes
             ,expArgs		= [] // expression arguments	
             ,op				= '' //if this remains blank, meaning it's a literal or attr. reference
             ,i, len, _expArgs = [], v;
            
        switch(true){
        
            case ( exp instanceof NODES.Op ) : {
                
                op = exp.operation.toUpperCase();
            
                expArgs = [exp.left, exp.right];
                
                //do this to save on recursive trips for the basic comparison operations
                if( '=' 	=== op || '==' === op || '>'	=== op ||
                    '<' 	=== op || '>=' === op || '<=' === op ||
                    '<>'	=== op || '!='  === op
                ){
                
                    var _getType = function(exp, NODES){
                        
                        if( exp instanceof NODES.LiteralValue ){
                            return 1;
                        }

                        //if exp is not an object, we assume it is not one of the following: Op, FunctionValue, FunctionCastValue, SubQuery
                        //thus, it is, is a true literal value.
                        if( typeof exp !== 'object' ){
                            return 0;
                        }
                        
                        return -1;
                        
                    };
                    
                    var lftType	= _getType(expArgs[0], NODES)
                         ,rgtType	= _getType(expArgs[1], NODES);
                    
                    //if left is a literal (i.e., field reference)
                    if( 1 === lftType ){
                        expArgs[0] = this._getFieldVal(tuple, expArgs[0].values.join("."), fields);
                    }
                    
                    //if right is a literal (i.e., field reference)
                    if( 1 === rgtType ){
                        expArgs[1] = this._getFieldVal(tuple, expArgs[1].values.join("."), fields);
                    }
                    
                    //if either one is not an Op, a function, a subquery (i.e., one or both is/are a field reference or a literal)
                    if( !( -1 === lftType || -1 === rgtType) ){
                        return this.supportedOperators[op].apply(this, expArgs);				
                    }

                }                
                
                if( op === 'IN' || op === 'NOT_IN' ){
                    //@todo: handle cases of correlated subqueries for IN operation			
                    expArgs[0] = this._evalExpInRel(expArgs[0], tuple, fields, allData, isInAggOp);
                    return this.supportedOperators[op].apply(this, expArgs);
                }
                
                break;
                
            }
            
            case ( exp instanceof NODES.FunctionValue ) : {
            
                op        = exp.name.toUpperCase();
                expArgs = exp.arguments;
                
                if( op === 'CASE' ){
                    return this.supportedOperators[op].apply(this, expArgs.concat([tuple, fields, allData, isInAggOp]));
                }
                
                if( this.aggOperators[op] ){
                    if( isInAggOp ){
                        throw new SQLError("could not prepare statement (1 misuse of aggregate function " + op + ")", _SQLErrorCodes.SYNTAX_ERR);
                    }                    
                    return this.supportedOperators[op].apply(this, [expArgs, tuple, fields, allData, isEmptyOnAgg]);                    
                }
                
                break;
            }
            
            case ( exp instanceof NODES.FunctionCastValue ) : {
                op = exp.name.toUpperCase();
                expArgs = [exp.expression, exp.toType.value];
                break;
            }

            case (exp instanceof  NODES.SubQuery ) : {
            
                //replace a correlated item's data in the subquery's plan.
                var ep = exp.ep;
                for(i = 0, len = ep.length; i < len; i++){
                    //skip if the plan is not a correlation
                    if( !ep[i].isCorrelated ){
                        continue;
                    }
                    //set rows and indexes to undefined
                    //so that the actual rows and indexes for JOIN process are reset to the current row.
                    baseLib.apply(exp.ep[i] , {data : [tuple], rows : undefined, indexes : undefined});
                }
                
                return this._procSubQuery(exp.ep, exp.pt, false);
                
            }
            
            //an attribute reference or a (true) literal was encountered.
            default : {
            
                if( exp === undefined || exp === null ){
                    return null;
                }
                
                return (undefined !== exp.values ? 
                          //field reference					  
                          this._getFieldVal(tuple, exp.values.join("."), fields) :
                          //the true literal
                          (exp.value === undefined ? exp : exp.value));                          
            }
            
        }
        
        //var _expArgs = [], v;
        for(i = 0, len = expArgs.length; i < len; i++){			
            v = this._evalExpInRel(expArgs[i], tuple, fields, allData, isInAggOp, isEmptyOnAgg);
            _expArgs = _expArgs.concat([v]);
        }

        return this.supportedOperators[op].apply(this, _expArgs);
        
    
    }
    
    //process cast
    ,_cast : function(args){
    
        var knownTypes = {
                'INT' : function(v) { return this._parseValue(v, 'integer'); }
               ,'INTEGER' : function(v) { return this._parseValue(v, 'integer'); }
               ,'UNSIGNED' : function(v) { return this._parseValue(v, 'integer'); }
               ,'TEXT' : function(v) { return v.toString(); }
            }
           ,arg 	  = args[0]
           ,val	  = arg.val
           ,toType = arg.toType.toUpperCase();
           
        var fn = knownTypes[toType];  
        if( undefined ===  fn){
            throw new SQLError("Unknown type '" + toType + "' used in CAST");
        }
        
        //try native
        
        
        return knownTypes[toType].apply(this, [val]);
        
    }
    
    ,_ifNull : function(val, subVal){
    
        return val !== null ? val : subVal;
    }

    //parse to actual data type; sometimes a value is string but must be saved as int. this function is for that.
    //@todo: to be refactored to CAST
    ,_parseValue : function(v, toType){
    
        if( v === null || v === undefined ){
            return v;
        }
        
        toType = toType !== undefined ? toType.toLowerCase() : toType;
        if( toType === 'real' || toType === 'integer' ){
            var _v = toType === 'real' ? parseFloat(v) : parseInt(v, null);
            return isNaN(_v) ? v : _v;
        }
        
        if( toType === 'string' || toType === 'text' ){
            return v.toString();
        }
        
        return v; 
        
    }
    
    ,_insertToTable : function(name, optimization, callbacks, sqlTrxn){

        var insertTree	= optimization.newParseTree
             ,insExp			= insertTree.insertExpression
             ,table			= this._getDBObj(name, 'table')
             ,tblSch			= table.schema
             ,tblData		= table.data
             ,rows			= []
             ,allFields 		= tblSch.columns
             ,row_			= {}
             //if the field is in the parse tree, build the indexed fields; else, use the one from the schema
             ,fields			= insertTree.fields ? (function(fields){
                                        var _f = [];
                                        baseLib.each(fields, function(idx, item, items){
                                            _f = _f.concat([item.name.value.toUpperCase()]);
                                        });
                                        return _f;
                                    }(insertTree.fields)) : tblSch.fIndex;

        if( baseLib.isArray(insExp) ){
            row_ = {};
            baseLib.each(insExp, function(idx, item, items){
                row_[idx.toString()] = item.value;
            }, this);
            rows = rows.concat([row_]);
        }
        else if ( insExp instanceof sqlParser.nodes.Select ){
            var result	= this._procSelectExp(insExp.ep, insExp.pt);
            rows  		= result.data;			
        }
        
        var _rowDefaults = {};		
        baseLib.each(allFields, function(idx, _field, _fields){
            _rowDefaults[idx] = null;
        }, this);
            
        var pkField 					= tblSch.pkField || ''
             ,autoIncField 			= tblSch.autoIncField || ''
             ,lastInsertId 			= tblSch.lastInsertId || 0
             ,_keys 					= tblSch.keys || {}
             ,_nonNullFields 		= tblSch.nonNullFields || {}
             ,_nonNullFieldsCnt 	= baseLib.count(_nonNullFields)
             ,newData 				= []
             ,finalRow 					= {}
             ,i, len, j, fn, f, val;

        var handleNonNullFields = function(idx, _nnf, _nnfs){
            //if field is not an autoinc field, and it is null
            if ( autoIncField !== idx && finalRow[idx] === null ){
                throw new SQLError("could not execute statement due to a constaint failure (19 constraint failed)", _SQLErrorCodes.CONSTRAINT_ERR);
            }
        };
        
        row_ = {};
        for(i = 0, len = rows.length; i < len; i++){
        
            finalRow = {};
            row_	 = rows[i];

            j = 0;
            for(fn in row_){
                f = allFields[fields[j]];
                finalRow[f.name] = this._parseValue(this._evalExpInRel(row_[fn]), f.type);
                j++;
            }
            
            finalRow = baseLib.applyIf(finalRow, _rowDefaults);
            
            if( _nonNullFieldsCnt > 0 ){
            
                baseLib.each(_nonNullFields, handleNonNullFields, this);
                
            }            

            if( pkField !== '' ) {
                
                if( _keys[finalRow[pkField]] !== undefined ){
                    throw new SQLError("could not execute statement due to a constaint failure (19 constraint failed)", _SQLErrorCodes.CONSTRAINT_ERR);
                }
                
                //technically, autoIncField == pkField is always true.
                //also, a PK field (regardless if declared as autoinc) is automatically an autoinc field during runtime
                //if user did not specify a value for the PK field during insert and it is declared to be of type "INTEGER".
                if( allFields[pkField].type.toUpperCase() === 'INTEGER' ){
                    if( finalRow[pkField] === null ){
                        ++lastInsertId;
                        finalRow[pkField] = lastInsertId;
                    }
                    else{                
                        lastInsertId = finalRow[pkField];
                    }
                }
                
                _keys[finalRow[pkField]] = true;
                
            }
            
            //keep track of distinct values			
            for(fn in finalRow){
            
                f		= allFields[fn];
                val	= finalRow[fn];
                
                if( undefined === f.distinctValues[val] ){
                    f.distinctValues[val] = true;
                    f.numOfDistinctValues++;
                }
                
            }
            
            newData = newData.concat(finalRow);

        }

        table = baseLib.apply(table, {
            data : tblData.concat(newData)
           ,schema : baseLib.apply(tblSch, {
                keys : _keys
               ,lastInsertId : lastInsertId
           })
        });

        //if we are not able to generate a last insert id up to this point,
        //the last insert id is the record number of the latest inserted record.
        if( lastInsertId === 0 ){
            lastInsertId = tblData.length + newData.length;
        }
        
        this._persistDBObj('table', table, {rowsAffected : newData.length, insertId : lastInsertId}, callbacks, sqlTrxn);
        
    }

    ,_updateTableData : function(name, optimization, callbacks, sqlTrxn){
    
        var updateTree			= optimization.newParseTree
             ,assignmentList	= updateTree.assignList
             ,updCond 			= updateTree.updateCondition
             ,condition				=	updCond instanceof sqlParser.nodes.Where ? 
                                            updCond.conditions : true
             ,table					= this._getDBObj(name, 'table')
             ,tblData				= table.data
             ,tblSch 				= table.schema
             ,lastInsertId 		= tblSch.lastInsertId || 0
             ,_keys 				= tblSch.keys || {}
             ,rowsToUpdate	= []
             ,rowsToUpdLen	= 0
             ,usingRowID			= false
             ,ra 						= 0;
        
        if( condition !== false ){
        
            rowsToUpdate = [].concat(tblData);

            var ep = optimization.executionPlan;
            if( ep.length > 0 && ep[0].pre && ep[0].pre.length > 0 ){
                var result = {};
                this._setupPlanData(ep[0], [], result, {rowIDOnly : true});
                rowsToUpdate = result.rows;
                usingRowID = true;				
            }
            
            rowsToUpdLen	= rowsToUpdate.length;
        }
        
        if( rowsToUpdLen > 0 ){
        
            var pkField 					= tblSch.pkField || ''
                 ,autoIncField 			= tblSch.autoIncField || ''
                 ,_nonNullFields			= tblSch.nonNullFields || {}
                 ,_nonNullFieldsCnt	= baseLib.count(_nonNullFields)
                 ,allFields					= updateTree.fields
                 ,otherCond				= typeof updCond === 'object'  ? updCond.otherCond : []
                 ,otherCondLen			= otherCond.length
                 ,i, len, rtuIdx, _row, row, isOk, j, jLen, cond
                 ,op, fName, newVal;
                 
            for(i = 0, len = rowsToUpdLen; i < len; i++){
                
                rtuIdx	= usingRowID ? rowsToUpdate[i] : i;
                row	= tblData[rtuIdx]; //the row to be updated
                _row	= baseLib.apply({}, row); // the original row
                isOk	= true;
                
                if( otherCondLen > 0 ){
                    for(j = 0; j < otherCondLen; j++){
                        cond = otherCond[j];
                        isOk  = this._evalExpInRel(cond, _row, allFields);
                        if( !isOk ){
                            break;
                        }
                    }				
                }
                
                if( !isOk ){
                    continue;
                }
                
                for(j = 0, jLen = assignmentList.length; j < jLen; j++){
                            
                    op		= assignmentList[j];
                    fName	= op.left.value.toUpperCase();
                    newVal	= this._evalExpInRel(op.right, _row, allFields);

                    if( newVal === null && _nonNullFieldsCnt > 0 ){
                        if( _nonNullFields[fName] ){
                            throw new SQLError("could not execute statement due to a constaint failure (19 constraint failed)", _SQLErrorCodes.CONSTRAINT_ERR);
                        }					
                    }
                        
                    if( fName === pkField ){
                        if( _keys[newVal] !== undefined ){
                            throw new SQLError("could not execute statement due to a constaint failure (19 constraint failed)", _SQLErrorCodes.CONSTRAINT_ERR);
                        }
                        delete _keys[_row[pkField]];
                        _keys[newVal] = true;
                    }

                    //update the last insert id if either pk field or autoinc field was specified
                    if( fName === autoIncField || fName === pkField ){
                        lastInsertId = newVal;
                    }
                        
                    row[fName] = newVal;
                        
                }
                
                ra++;

                tblData[rtuIdx] = row;
            
            }
            
        }
        
        table = baseLib.apply(table, {
            data : tblData
           ,schema : baseLib.apply(tblSch, {
                keys : _keys
               ,lastInsertId : lastInsertId
           })
        });
        
        this._persistDBObj('table', table, {rowsAffected : ra, insertId : lastInsertId}, callbacks, sqlTrxn);
        
    }

    ,_deleteFromTable : function(name, optimization, callbacks, sqlTrxn){
        
        var deleteTree			= optimization.newParseTree
             ,delCondition		= deleteTree.deleteCondition
             ,condition				= delCondition instanceof sqlParser.nodes.Where ? delCondition.conditions : true
             ,table					= this._getDBObj(name, 'table')
             ,tblSch					= table.schema
             ,autoIncField 	    = tblSch.autoIncField || ''
             ,lastInsertId 	    = tblSch.lastInsertId || 0             
             ,_keys					= tblSch.keys || {}
             ,tblData				= table.data
             ,ra						= 0
             ,usingRowID			= false
             ,rowsToDelete		= []
             ,rowsToDelLen		= 0;

        if( condition !== false ){
        
            rowsToDelete = [].concat(tblData);
            /*
                Pre-filter the records to be deleted using the pre condition (i.e "simple conditions").
                Also, the searching of the rows to be deleted will be using the index.
            */
            var ep = optimization.executionPlan;
            if( ep.length > 0 && ep[0].pre && ep[0].pre.length > 0 ){
                var result = {};
                this._setupPlanData(ep[0], [], result, {rowIDOnly : true});
                rowsToDelete = result.rows;
                usingRowID = true;				
            }
            
            rowsToDelLen	= rowsToDelete.length;
        }
        
        if( rowsToDelLen > 0 ){
        
            var otherCond			= typeof delCondition === 'object' ? delCondition.otherCond : []
                 ,otherCondLen		= otherCond.length
                 ,pkField				= tblSch.pkField || ''
                 ,allFields				= deleteTree.fields
                 ,cremove				= fnNameRemove
                 /*
                    setup a separate copy so that if each time we delete a row, we have the original copy
                    and the indexes are intact.
                 */
                 ,_tblData				= [].concat(tblData)
                 ,i, rtdIdx, row, isOk, j, jLen, cond, key;
                 
            /*
                delete from the last to keep up with the updated remainingRows array.
                This technique works if it is assured that the items in rowsToDelete, if it's used, is sorted from ascending order,
                so that during runtime it will be accessed in descending manner.
            */
            for(i = rowsToDelLen - 1; i > -1; i--){
            
                rtdIdx	= usingRowID ? rowsToDelete[i] : i; //the idx of the row to be deleted.
                row	= _tblData[rtdIdx]; //the actual row to be deleted.
                isOk	= true;
                
                /*
                    if there are other conditions, evaluate them. if at least 1 results to FALSE, do not
                    evaluate the rest (if any); also, do not continue the operation.
                */
                if( otherCondLen > 0 ){
                    for(j = 0, jLen = otherCondLen; j < jLen; j++){
                        cond = otherCond[j];
                        isOk	= this._evalExpInRel(cond, row, allFields);						
                        if( !isOk ){
                            break;
                        }
                    }
                }
                
                if( !isOk ){
                    continue;
                }
                
                if( pkField ){
                    key = row[pkField];
                    if( _keys[key] !== undefined ){
                        delete _keys[key];
                    }
                }

                tblData[cremove](rtdIdx);
                ra++;
                
            }
            
        }
        
        table = baseLib.apply(table, {
            data : tblData
           ,schema : baseLib.apply(tblSch, {
                keys : _keys
               //reset to 0 if all records were deleted and no autoInc field was declared.
               ,lastInsertId : rowsToDelLen === ra && !autoIncField ? 0 : lastInsertId
            })
        });
        
        this._persistDBObj('table',  table, {rowsAffected :ra, insertId : undefined}, callbacks, sqlTrxn);
        
    }
    
    ,_createTable : function(name, args, callbacks, sqlTrxn){
    
        if( this._isDBObjDefined(name, 'table') ){
            if( args.ifNotExists ) {
                if( typeof callbacks.cbSuccess === 'function' ){
                    callbacks.cbSuccess.apply(context, [sqlTrxn, new SQLResultSet({rowsAffected : 0, insertId : undefined})]);
                }
                return;
            }
            //note: testing in chrome yields SYNTAX_ERR for attempting to create an existing table
            throw new SQLError("could not prepare statement (1 table " + name + " already exists)", _SQLErrorCodes.SYNTAX_ERR);
        }

        var sql = sqlTrxn.sql.replace(/^create table/i, 'CREATE TABLE');
        sql = sql.replace(/^create table if not exists/i, 'CREATE TABLE');

        var data =  []
           ,schema = {
                columns : args.fields
               ,fIndex : args.fIndex
               ,fIndexOrigNames : args.fIndexOrigNames
               ,pkField : args.pkField
               ,autoIncField : args.autoIncField
               ,keys     : args.keys
               ,nonNullFields : args.nonNullFields
               ,lastInsertId : 0
               ,sql : sql
               ,tableOrigName : name
            };
        
        this._persistDBObj('table', {schema : schema, data : data, name : name}, {rowsAffected : 0, insertId : undefined}, callbacks, sqlTrxn, false, true);
        
    }
    
    ,_dropTable : function(name, args, callbacks, sqlTrxn){

        if(  !this._isDBObjDefined(name, 'table') ){
            if( args.ifExists === false ){
                //note: testing in chrome yields SYNTAX_ERR for attempting to drop a non-existent table
                throw new SQLError("could not prepare statement (1 no such table: " + name + ")", _SQLErrorCodes.SYNTAX_ERR);
            }
            
            if( typeof callbacks.cbSuccess === 'function' ){
                callbacks.cbSuccess.apply(context, [sqlTrxn, new SQLResultSet({rowsAffected : 0, insertId : undefined})]);
            }

            return;
        }

        this._deleteDBObj('table', {name : name}, {rowsAffected : 0, insertId : undefined}, callbacks, sqlTrxn);

    }
    
    ,_renameTable : function(name, pt, callbacks, sqlTrxn){
        
        if(  !this._isDBObjDefined(name, 'table') ){
            throw new SQLError("could not prepare statement (1 no such table: " + name + ")", _SQLErrorCodes.SYNTAX_ERR);
        }
        
        var newName = pt.newName.value;

        if( this._isDBObjDefined(newName, 'table') ){         
            throw new SQLError("could not prepare statement (1 there is already another table or index with this name: " + newName + ")", _SQLErrorCodes.SYNTAX_ERR);
        }
        
        var me = this
             ,tbl = this._getDBObj(name, 'table')
             ,oschema = tbl.schema
             ,schema = baseLib.apply(oschema, {
                tableOrigName : newName
               ,sql : oschema.sql.replace(name, '"' + newName + '"') //enclose the name in double quotes since this is the behavior in WebSQL (chrome)
              })
             ,newTable = {schema : schema, data : tbl.data, name : newName}             
             ,createCallback = {
                cbSuccess : function(){
                    //2.) drop the old table.
                    me._deleteDBObj('table', {name : name}, {rowsAffected : 0}, callbacks, sqlTrxn);
                }
               ,cbError : function(){
                    if( callbacks.cbError ){
                        callbacks.cbError.apply(context, arguments);
                    }
                }
             };
        
        //1.) create a new table using schema of old table.
        this._persistDBObj('table', newTable, {rowsAffected : 0, insertId : undefined}, createCallback, sqlTrxn, false, true);
        
    }
    
    //abstract
    //,_isDBObjDefined : function(name, objecType){}
    
    //abstract
    //,_getDBObj : function(name, objectType){}
    
    //abstract
    //,_persistDBObj : function(objectType, def, result, callbacks, sqlTrxn, isFromInit){}
    
    //abstract
    //,readFromStorage : function(tableName){}
    
    //abstract
    //,_deleteDBObj : function(objectType, def, result, callbacks, sqlTrxn){}
    
    ,_schemaToCreateSQL : function(schema){
        
        var fields = schema.columns
            ,tblName = schema.tableOrigName
            ,sql = "CREATE TABLE " + tblName
            ,_fields = [], fldDef = ""
            ,fldID, fldObj;
        
        for(fldID in fields){
        
            fldObj = fields[fldID];
            fldDef = fldObj.nameOrig + " " + fldObj.type;
            
            if( fldObj.isNullable === false ){
                fldDef += " not null";
            }
            
            if( fldObj.isPK === true ){
                fldDef += " primary key";
            }
            
             if( fldObj.isAutoInc === true ){
                fldDef += " autoincrement";
            }
            
            _fields = _fields.concat([fldDef]);
            
        }
        
        
        return sql + "(" + _fields.join(", ") + ")";
        
    }
    
    ,sqlite_master : null
    
    ,_sqliteMasterUpdate : function(name, dbObj, mode){
        
        var sqlite_master = this.sqlite_master;
        
        if( !sqlite_master ){
            sqlite_master = {
                schema : {
                    autoIncField : ''
                   ,columns : {
                        TYPE : {
                            alias : 'type'
                           ,aliasOrig : 'TYPE'
                           ,distinctValues : {}
                           ,isAmbiguous : false
                           ,isAutoInc : false
                           ,isNullable : false
                           ,isPK : false
                           ,mapping : 'TYPE'
                           ,maxVal : 0
                           ,minVal : 0
                           ,name : 'TYPE'
                           ,nameDisplay : 'type'
                           ,nameOrig : 'type'
                           ,numofDistinctValues : 0
                           ,ordPosition : 0
                           ,type : 'text'
                        }
                       ,NAME : {
                            alias : 'name'
                           ,aliasOrig : 'NAME'
                           ,distinctValues : {}
                           ,isAmbiguous : false
                           ,isAutoInc : false
                           ,isNullable : false
                           ,isPK : false
                           ,mapping : 'NAME'
                           ,maxVal : 0
                           ,minVal : 0
                           ,name : 'NAME'
                           ,nameDisplay : 'name'
                           ,nameOrig : 'name'
                           ,numofDistinctValues : 0
                           ,ordPosition : 1
                           ,type : 'text'
                       }
                       ,TBL_NAME : {
                            alias : 'tbl_name'
                           ,aliasOrig : 'TBL_NAME'
                           ,distinctValues : {}
                           ,isAmbiguous : false
                           ,isAutoInc : false
                           ,isNullable : false
                           ,isPK : false
                           ,mapping : 'TBL_NAME'
                           ,maxVal : 0
                           ,minVal : 0
                           ,name : 'TBL_NAME'
                           ,nameDisplay : 'tbl_name'
                           ,nameOrig : 'tbl_name'
                           ,numofDistinctValues : 0
                           ,ordPosition : 2
                           ,type : 'text'
                       }
                       ,ROOTPAGE : {
                            alias : 'rootpage'
                           ,aliasOrig : 'ROOTPAGE'
                           ,distinctValues : {}
                           ,isAmbiguous : false
                           ,isAutoInc : false
                           ,isNullable : false
                           ,isPK : false
                           ,mapping : 'ROOTPAGE'
                           ,maxVal : 0
                           ,minVal : 0
                           ,name : 'ROOTPAGE'
                           ,nameDisplay : 'rootpage'
                           ,nameOrig : 'rootpage'
                           ,numofDistinctValues : 0
                           ,ordPosition : 3
                           ,type : 'integer'
                       }
                       ,SQL : {
                            alias : 'sql'
                           ,aliasOrig : 'SQL'
                           ,distinctValues : {}
                           ,isAmbiguous : false
                           ,isAutoInc : false
                           ,isNullable : false
                           ,isPK : false
                           ,mapping : 'SQL'
                           ,maxVal : 0
                           ,minVal : 0
                           ,name : 'SQL'
                           ,nameDisplay : 'sql'
                           ,nameOrig : 'sql'
                           ,numofDistinctValues : 0
                           ,ordPosition : 4
                           ,type : 'text'
                       }
                   }
                  ,fIndex : ['TYPE', 'NAME', 'TBL_NAME', 'ROOTPAGE', 'SQL']
                  ,fIndexOrigNames : {
                        TYPE : 'type'
                       ,NAME : 'name'
                       ,TBL_NAME : 'tbl_name'
                       ,ROOTPAGE : 'rootpage'
                       ,SQL : 'sql'
                   }
                  ,keys : {}
                  ,lastInsertId : 0
                  ,nonNullFields : {}
                  ,pkField : ''
                  ,sql : ''
                }
               ,data : []
               ,name : 'sqlite_master'
               ,entries : []
            };
        }

        var entryIdx = sqlite_master.entries.indexOf(name);
            
        if( -1 < entryIdx ){
            var removeFn = fnNameRemove;
            sqlite_master.data[removeFn](entryIdx);
            sqlite_master.entries[removeFn](entryIdx);
        }
        
        if( mode === 'PUT' ){
        
            var origName = dbObj.schema.tableOrigName || name
                ,data = sqlite_master.data;
                
            sqlite_master.entries.push(name);
            sqlite_master.data = data.concat([{
                TYPE : 'table'
               ,NAME : origName
               ,TBL_NAME : origName
               ,ROOTPAGE : data.length + 1
               /*
               if for some reason (e.g., old db definition) there is no SQL specified in the schema, then generate one.
               this is primarily for backward compat
               */
               ,SQL : dbObj.schema.sql || this._schemaToCreateSQL(dbObj.schema)
            }]);
            
        }

        this.sqlite_master = sqlite_master;
        
    }
    
});

/**
@class: LocalStorage
@parent: AbstractWebStorage

This class handles storage concerns when the chosen storage mechanism/driver is LocalStorage.
*/
LocalStorage = AbstractWebStorage.extend({

    init : function(cfg){
        
        baseLib.apply(this, cfg);

    }
    
    ,_locStor : null
    
    ,_storage : function(){
   
        if( !this._locStor ) {
            this._locStor = context.localStorage;
        }
        
        return this._locStor;
        
    }

    ,_prepare  : function(onReady, scope){
    
        if( !context.JSON ){
        
            if( typeof define === 'function' && define.amd ){
            
                require(['json2'], function(json){
                    context.JSON = json;
                    onReady.apply(scope, []);
                });
                
                return;
            }
            
            throw new SQLError("The storage driver 'LocalStorage' requires JSON encoder and decoder.", _SQLErrorCodes.DATABASE_ERR);
        }
        
        var mode = this.mode ? this.mode.toLowerCase() : 'async';
        if( mode === 'sync' ){
            onReady.apply(scope, []);
            return;
        }
        
        setTimeout(function(){
            onReady.apply(scope, []);
        }, 0);
        
    }
    
    ,_persistDBObj : function(objectType, def, result, callbacks, sqlTrxn, isFromInit, isCreateTable){
    
        isFromInit = isFromInit || false;
        isCreateTable = isFromInit || isCreateTable;
        
        var exception = null, size = 0;
        if( objectType.toUpperCase() === 'TABLE' ){

            try{

                var stor = this._storage()
                    ,dbName = this.dbName
                    ,_def = {
                        "schema" : {"tables" : {}}
                       ,"data" : {"tables" : {}}
                    };
                    
                if( !isFromInit ){
                    var tableName = def.name;
                    _def.schema.tables[tableName] = def.schema;
                    _def.data.tables[tableName] = {data : def.data};
                }
                else{
                    _def = def;
                }
                
                var schemaTables = _def.schema.tables, dataTables = _def.data.tables, key, data
                     ,completeName, dbObj;
                for(key in schemaTables){
                    
                    completeName = (dbName + "." + objectType + "." + key).toUpperCase();
                    dbObj = {schema : schemaTables[key], data : dataTables[key].data, name :  key.toUpperCase()};
                    
                    stor.removeItem(completeName);
                    data = JSON.stringify(dbObj);
                    size = size + data.length;
                    stor.setItem(completeName, data);
                    
                    if( isCreateTable ){
                        this._sqliteMasterUpdate(completeName, dbObj, 'PUT');
                    }

                }

            }catch(e){
                exception = e;
            }

        }
        
        size = Math.round((size/1024/1024) * 100) / 100;
        var info = {dataSize : size};

        if( !exception ){
            if( typeof callbacks.cbSuccess === 'function' ) {
                callbacks.cbSuccess.apply(context, [sqlTrxn, new SQLResultSet(result), info]);
            }
            return;
        }

        if( typeof callbacks.cbError === 'function' ) {
            
            var msg =exception.message.toString().trim(), code = exception.code;
            
            // aside from the standard DOM error, occasionally this message appears on IE9 when data is too large.
            info.isSpecialError = (msg === "Not enough storage is available to complete this operation.");
            
            if( 
                exception.code === 22 ||
                info.isSpecialError
            ){
                
                code = _SQLErrorCodes.TOO_LARGE_ERR;
                
                msg =  "Current data size exceeds the limit for the current database version";
            }

            callbacks.cbError.apply(context, [sqlTrxn, new SQLError(msg, code), info]);

        }

    }
    
    ,_deleteDBObj : function(objectType, def, result, callbacks, sqlTrxn){
    
        try{
        
            var completeName = ([this.dbName, objectType, def.name].join('.')).toUpperCase();
            
            this._storage().removeItem(completeName);
            
            this._sqliteMasterUpdate(completeName, null, 'DELETE');
            
            if( typeof callbacks.cbSuccess === 'function' ) {
                callbacks.cbSuccess.apply(context, [sqlTrxn, new SQLResultSet(result)]);
            }
        }
        catch(e){
             if( typeof callbacks.cbError === 'function' ) {
                 callbacks.cbError.apply(context, [sqlTrxn, new SQLError(e.message.toString().trim(), e.code), {}]);
             }
        }
        
    }
    
    ,_mInit : function(def, success, error){
   
        var obj = this;
        var fn = function(){
            var ra = def.rowsAffected || 0;
            delete def.rowsAffected;
            
            obj._persistDBObj('table', def, {rowsAffected : ra}, {cbSuccess : success, cbError : error }, new SQLTransaction(null), true);
        };

        this._prepare(fn, this);
   }

    //read user data
    ,readFromStorage : function(tableName){

        var obj = this._getDBObj(tableName, 'table');
        
        if( !obj ){
            throw new SQLError("could not prepare statement (1 no such table: " + tableName + ")", _SQLErrorCodes.SYNTAX_ERR);
        }
        
        return obj.data;

    }
    
    ,_isDBObjDefined : function(name, objectType){
    
        return this._getDBObj(name, objectType) !== null;

    }
    
    ,_getDBObj : function(name, objectType){
        

        var stor = this._storage()
            ,obj  = stor.getItem((this.dbName + "." + objectType + "." + name).toUpperCase());
        
        return JSON.parse(obj || null);		
        
    }	
    
    ,_sqliteMasterUpdate : function(name, dbObj, mode){

        this._super(name, dbObj, mode);
        
        name = [this.dbName, 'TABLE', 'sqlite_master'].join('.').toUpperCase();
        
        this._storage().setItem(name, JSON.stringify(this.sqlite_master));
        
    }
    
});

/**
@class: IndexedDB
@parent: AbstractWebStorage

This class handles storage concerns when the chosen storage mechanism/driver is IndexedDB.
*/
IndexedDB = AbstractWebStorage.extend({

    _idxDB: null
 
    , init: function (config) {
        
        baseLib.apply(this, config);
                
        this._createDB(this.dbName);
        
    }

    ,_isDBLoaded : false
    ,requestTransaction : function(sqlTrxnCallback){
    
        var intervalID, me = this, attempts = 0;

        var _handleReady = function(){
        
            if( me._isDBLoaded ){
                sqlTrxnCallback();
            }
            else{
                me._loadDBObjects(sqlTrxnCallback);
            }
            
        };
        
        var _checkDBIfReady = function(){
        
            if( me._isReady ){
            
                clearInterval(intervalID);
                _handleReady();

                return;
                
            }
        
            if( attempts === 100 ){
                clearInterval(intervalID);
                throw new SQLError("Cannot grant transaction, db not ready.");
            }
            
            attempts++;
            
        };

        if( me._isReady ){
            _handleReady();
        }
        else{
            intervalID = setInterval(_checkDBIfReady, 5);
        }
        
    }
    
    ,_isLoadingDBObj : false	
    ,_queues : []	
    ,_loadDBObjects : function(sqlTrxnCallback){
    
        var me = this, dbName = this.dbName;
        
        var fireEvent = function(me){
        
            me._isLoadingDBObj = false;
            me._isDBLoaded = true;
            var queues = me._queues, cremove = fnNameRemove;
            while( queues.length > 0 ){
                setTimeout(queues[0], 0);
                queues[cremove](0);
            }
            
        };
        
        me._queues = me._queues.concat([sqlTrxnCallback]);

        if( me._isDBLoaded ){
            fireEvent(me);
            return;
        }

        if( me._isLoadingDBObj ){
            return;
        }
        
        me._isLoadingDBObj = true;		
        
        me._dbObjs = me._dbObjs || {};
        
        /**
        
            If the storage driver used is IndexedDB, we create one  IndexedDB Database with one ObjectStore (i.e., "table") in it.
            Both Database and ObjectStore are named using the user-supplied database name. The entries/rows in the ObjectStore
            are the database objects created by the user.
            A row in the sole ObjectStore contains the data and meta-data of a user-defined table.
            
            What the code below does is to access both IndexedDB Database and ObjectStore.
            If the ObjectStore is not yet present, then create it -- as
            specified by the boolean TRUE in the second argument in the method call .objectStore(this.dbName, true).
            Then, the number of entries in the ObjectStore is retrieved, assigning it to the variable numOfItems. 
            Then, for each entries in the ObjecStore (that is, for each user-defined tables), we load it to an instance variable.
            After everything is loaded, the function _onReady is called signifying that the request db operation can now be executed.
            
        */
        me._idxDB(dbName).objectStore(dbName, true).count().done(function(numOfItems){			
        
            if( numOfItems === 0 ){
                fireEvent(me);
                return;
            }
            
            var count = 0;
        
            me._idxDB(dbName).objectStore(dbName).each(function(item){               

                count++;
                me._persistDBObjMemory(item.value.component, item.value.data, 'PUT', true);
                
                if( count === numOfItems ){
                    fireEvent(me);
                    return;
                }

            });
            
        });
        
    }
    
    ,_prepare : function(onReady, scope){
    
        setTimeout(function(){
            onReady.apply(scope, []);
        }, 0);
        
    }

    ,_dbObjs : null
    ,_dbObjects : function(){
        
        return this._dbObjs;
    }
    
    ,_isReady    : false
    ,_isOpening : false
    , _createDB : function (dbName) {
        
        var	me = this;
        
        var __createDB = function(){
        
            var idxDB	= me._idxDB;
            if( me._isOpening ){
                return;
            }
            
            idxDB(dbName, {
                "schema" : {
               
                    "1" : function (trans) {
                                            
                            me._isReady = false;
                            
                            trans.deleteObjectStore(dbName);
                                
                            var obj = trans.createObjectStore(dbName, {
                                keyPath: "component", autoIncrement: false
                            });

                            obj.createIndex("component", {"unique": true});
                                
                       }
                
                }
            }).done(function(){
                
                if( me._isReady === false ) {
                    me._isReady = true;
                    me._isOpening = false;
                }
                
            });        
            
            me._isOpening = true;
            
        };
        
        if( !this._idxDB ){

            var deps = [
                {name : 'jquery', lib : context.jQuery}
               ,{name : 'jqueryindexeddb', lib : context.jQuery ? context.jQuery.indexedDB : undefined}
            ], i, len = deps.length, missing = [], dep;
            
            for(i = 0; i < len; i++){
                dep = deps[i];
                if( deps[i].lib === undefined ) {
                    missing = missing.concat([dep.name]);
                }        
            }
            
            if( missing.length > 1 ){
                
                if( typeof define === 'function' && define.amd ){
                    require(missing, function(){
                        me._idxDB = context.jQuery.indexedDB;
                        __createDB();
                    });
                    return;
                }
                
                throw new SQLError("The storage driver 'IndexedDB' requires the following libraries: " + missing.join(','), _SQLErrorCodes.DATABASE_ERR);
           
            }
        }
        
        this._idxDB = context.jQuery.indexedDB;
        
        __createDB();
        
    }

    ,_persistDBObjMemory : function(name, dbObj, mode, isCreateOrDropTable){
        
        mode = mode || 'PUT';        
        mode = mode.toUpperCase();
        
        if( mode === 'PUT' ){
            this._dbObjs[name] = dbObj;
        }
        else{
            delete this._dbObjs[name];
        }
        
        if( !isCreateOrDropTable ){
            return;
        }

        this._sqliteMasterUpdate(name, dbObj, mode);
        this._dbObjs[[this.dbName, 'TABLE', 'sqlite_master'].join('.').toUpperCase()] = this.sqlite_master;

    }
    
    //persist/drop a data object (e.g., table)
    , _persistDBObj : function(objectType, def, result, callbacks, sqlTrxn, isFromInit, isCreateTable){

        var dbName 			= this.dbName
            ,me 					= this;
        
        isCreateTable = isFromInit || isCreateTable;
        
        var info = {};
             
        var handleSuccess = function(){
            if( typeof callbacks.cbSuccess === 'function' ){
                callbacks.cbSuccess.apply(context, [sqlTrxn, new SQLResultSet(result), info]);
            }
        };
        
        var handleError = function(/*err, e*/){
            if( typeof callbacks.cbError === 'function' ){
                callbacks.cbError.apply(context, [sqlTrxn, new SQLError('IndexedDB Error'), info]);
            }
        };
        
        var handlSetData = function(transaction){

            var _dbName 	= dbName
                 ,_def			= {
                    "schema" : {"tables" : {}}
                    ,"data" : {"tables" : {}}
                };
                    
            if( !isFromInit ){
                var tableName = def.name;
                _def.schema.tables[tableName] = def.schema;
                _def.data.tables[tableName] = {data : def.data};
            }
            else{
                _def = def;
            }
            
            var schemaTables 	= _def.schema.tables
                 ,dataTables 			= _def.data.tables
                 ,_key 					= _dbName + "." + objectType
                 ,db 						= transaction.objectStore(_dbName)
                 ,key, completeName, dbObj;

            for(key in schemaTables){
                    
                completeName = (_key + "." + key).toUpperCase();

                dbObj = {
                     schema : schemaTables[key]
                    ,data : dataTables[key].data
                    ,name :  key.toUpperCase()                
                };

                //persist in IndexedDB
                db.put({"component" : completeName, "data" : dbObj});
                
                //persist in memory
                me._persistDBObjMemory(completeName, dbObj, 'PUT', isCreateTable);
                
            }

        };
        
        if( objectType.toUpperCase() === 'TABLE' ){
        
            this._idxDB(dbName).transaction(dbName).then(
                handleSuccess
               ,handleError
               ,handlSetData
            );
            
        }
        
    }
    
    ,_deleteDBObj : function(objectType, def, result, callbacks, sqlTrxn){
    
        var dbName 			= this.dbName
            ,me = this;
            
        if( objectType.toUpperCase() === 'TABLE' ){
        
            this._idxDB(dbName).transaction(dbName).then(
                //success
                function(){
                    if( typeof callbacks.cbSuccess === 'function' ){
                        callbacks.cbSuccess.apply(context, [sqlTrxn, new SQLResultSet(result), {}]);
                    }
                }
                
                //error
               ,function(/*err, e*/){
                    if( typeof callbacks.cbError === 'function' ){
                        callbacks.cbError.apply(context, [sqlTrxn, new SQLError('IndexedDB Error'), {}]);
                    }
                }
                //set data
               ,function(transaction){
               
                    var completeName = (dbName + "." + objectType + "." + def.name).toUpperCase();
                    transaction.objectStore(dbName).delete(completeName);
                    me._persistDBObjMemory(completeName, null, 'DELETE', true);
                    
                }
               
            );
            
        }
        
    }
    
    ,_mInit : function(def, success, error){
       
        var obj = this;
        var fn = function(){
            var ra = def.rowsAffected || 0;
            delete def.rowsAffected;
            obj._persistDBObj('table', def, {rowsAffected : ra}, {cbSuccess : success, cbError : error }, new SQLTransaction(null), true);
        };

        this._prepare(fn, this);
    }	

    ,readFromStorage : function(tableName){

        var obj = this._getDBObj(tableName, 'table');
        
        if( !obj ){
            throw new SQLError("could not prepare statement (1 no such table: " + tableName + ")", _SQLErrorCodes.SYNTAX_ERR);
        }
        
        return obj.data;

    }
    
    ,_isDBObjDefined : function(name, objectType){
    
        return this._getDBObj(name, objectType) !== null;

    }
    
    ,_getDBObj : function(name, objectType){

        var _do = this._dbObjects();
        
        return _do[(this.dbName + "." + objectType + "." + name).toUpperCase()] || null;
        
    }
        
});


//properties to expose publicly
var pubProps = [
    'INVALID_STATE_ERR'
   ,'SECURITY_ERR'
   ,'openDatabase'
   ,'openDatabase2'
   ,'SQLErrorCodes'
], propKey, _webSQL = baseLib.webSQL, i, len;

for(i = 0, len = pubProps.length; i < len; i++){
    propKey = pubProps[i];
    context[propKey] = _webSQL[propKey];
}

if( typeof define === 'function' && define.amd ) {
    define('re_websql', [], function() {
        return _webSQL;
    });
}

}, this, 're'));